SQL Server查询数据库空间分配情况、数据库备份信息
查询数据库空间分配情况：
1 use master 2 go 3 create procedure dbo.proc_getdbspaceused 4 as 5 begin 6 set nocount on 7 create table #dbsize( 8 database_id int 9 ,database_name nvarchar(1024) 10 ,size_kb bigint 11 ,space_available_kb bigint 12 ,reserved_kb bigint 13 ,data_kb bigint 14 ,index_kb bigint 15 ,unused_kb bigint 16 ) 17 18 declare @database_id int 19 declare @name nvarchar(1024) 20 declare @sql nvarchar(max) 21 declare cur cursor for select database_id,name from master.sys.databases order by database_id 22 open cur 23 fetch next from cur into @database_id,@name 24 while @@fetch_status=0 25 begin 26 set @sql =N' 27 insert into #dbsize 28 select 29 database_id = '+CONVERT(nvarchar(10),@database_id)+', 30 database_name = '''+@name+''', 31 size_kb = ((dbsize + logsize) * 8192 / 1024), 32 space_available_kb = (case when dbsize >= reservedpages then ((dbsize-reservedpages)* 8192/ 1024) else 0 end), 33 reserved_kb = (reservedpages * 8192 / 1024), 34 data_kb = (pages * 8192.0 / 1024), 35 index_kb = ((usedpages - pages) * 8192 / 1024), 36 unused_kb = ((reservedpages - usedpages) * 8192 / 1024) 37 from( 38 select 39 dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) , 40 logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) 41 from ['+@name+'].dbo.sysfiles 42 ) t1,( 43 select 44 reservedpages = sum(a.total_pages), 45 usedpages = sum(a.used_pages), 46 pages = sum( 47 CASE 48 When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0 49 When a.type <> 1 Then a.used_pages 50 When p.index_id < 2 Then a.data_pages Else 0 51 END ) 52 from ['+@name+'].sys.partitions p 53 inner join ['+@name+'].sys.allocation_units a on p.partition_id = a.container_id 54 left join ['+@name+'].sys.internal_tables it on p.object_id = it.object_id 55 )t2' 56 exec(@sql) 57 fetch next from cur into @database_id,@name 58 end 59 close cur 60 deallocate cur 61 62 select * from #dbsize 63 end 64 go
1 exec master.dbo.proc_getdbspaceused
查询数据库备份信息：
1 -- drop table #dbsize 2 -- drop table #logsize 3 create table #dbsize( 4 database_id int 5 ,database_name nvarchar(1024) 6 ,size_kb bigint 7 ,space_available_kb bigint 8 ,reserved_kb bigint 9 ,data_kb bigint 10 ,index_kb bigint 11 ,unused_kb bigint 12 ) 13 go 14 create table #logsize( 15 database_name nvarchar(1024) 16 ,LogSize_MB decimal(24,8) 17 ,LogSpaceUsed decimal(14,8) 18 ,Status tinyint 19 ) 20 go 21 insert into #dbsize exec master.dbo.proc_getdbspaceused 22 go 23 insert into #logsize exec('DBCC sqlperf(logspace)') 24 go 25 select t0.database_id,t0.database_name 26 ,t0.size_kb/1024 as [数据库大小MB] 27 ,t0.space_available_kb/1024 as [可用空间MB] 28 ,LogSize_MB as [日志大小MB] 29 ,LogSpaceUsed as [日志已使用%] 30 ,t1.compatibility_level as [兼容级别] 31 ,t1.collation_name as [校对规则] 32 ,t1.recovery_model_desc as [恢复模式] 33 ,(case t3.type/*btype*/ when 'D' then '完整' when 'I' then '差异' when 'L' then '日志' end)as [备份模式] 34 ,最近备份时间,备份大小MB,备份耗时Min,备份目录 35 from #dbsize t0 36 inner join master.sys.databases t1 on t0.database_name=t1.name and t1.state_desc='ONLINE' 37 inner join #logsize t2 on t0.database_name=t2.database_name 38 --LEFT JOIN (select btype from (values('D'),('I'),('L')) as btype(btype)) t4 on 1=1 39 left join ( 40 select a.database_name,a.type 41 ,CONVERT(varchar(30),a.backup_start_date,120) AS 最近备份时间 42 ,convert(decimal(18,2),compressed_backup_size/1024/1024.) as 备份大小MB 43 ,DATEDIFF(MINUTE,backup_start_date,backup_finish_date) as 备份耗时Min 44 ,REVERSE(stuff(REVERSE(physical_device_name),1,CHARINDEX('\',REVERSE(physical_device_name))-1,'')) as 备份目录 45 from msdb.dbo.backupset a 46 inner join ( 47 select database_name,type,MAX(backup_start_date) as latest_backup_date 48 from msdb.dbo.backupset 49 group by database_name,type 50 )b on a.database_name=b.database_name and a.type=b.type and a.backup_start_date=b.latest_backup_date 51 left join msdb.dbo.backupmediafamily c on a.media_set_id=c.media_set_id 52 )t3 on t0.database_name=t3.database_name --and t2.btype=t3.type 53 go
