SQL Server：查询数据库空间使用情况、数据库备份信息

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server：查询数据库空间使用情况、数据库备份信息，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5080字，纯文字阅读大概需要8分钟。

内容图文

在SQL Server数据库中，如何查询数据库空间使用情况，如何查询数据库备份信息？

1.新建一个获取数据库使用空间的存储过程，代码如下

 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  

2. 使用临时表语句块，调用存储过程查询数据库空间使用情况和数据库备份信息：

 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

3.使用msdb库备份表直接查询（不使用存储过程）数据库备份信息

select tt.database_name [数据库名],tt.type [备份类型],
convert(varchar,tt.backup_start_date,121) as [备份开始时间],
convert(varchar,tt.backup_finish_date,121) as [备份完成时间],tt.name [备份名称] 
from msdb.dbo.backupset tt
--where tt.database_name in ('master','model','msdb') 
--and convert(varchar,tt.backup_start_date,121)>='2019-01-01 00:00:00'  
order by tt.database_name,tt.backup_start_date

以上部分资料参考自网络。

内容总结

以上是互联网集市为您收集整理的SQL Server：查询数据库空间使用情况、数据库备份信息全部内容，希望文章能够帮你解决SQL Server：查询数据库空间使用情况、数据库备份信息所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---