SQLServer 维护脚本分享（12）查看数据库空间分配情况（准确）

概述use mastergocreate procedure dbo.proc_getdbspaceusedasbeginset nocount oncreate table #dbsize( database_id int ,database_name nvarchar(1024) ,size_kb bigint ,space_available_kb bigint ,res

use mastergocreate procedure dbo.proc_getdbspaceusedasbeginset nocount oncreate table #dbsize(	 database_ID int,database_name nvarchar(1024),size_kb bigint,space_available_kb bigint,reserved_kb bigint,data_kb bigint,index_kb bigint,unused_kb bigint)declare @database_ID intdeclare @name nvarchar(1024)declare @sql nvarchar(max)declare cur cursor for select database_ID,name from master.sys.databases order by database_IDopen curfetch next from cur into @database_ID,@namewhile @@fetch_status=0beginset @sql =N'insert into #dbsizeselect	database_ID = '+CONVERT(nvarchar(10),@database_ID)+',database_name = '''+@name+''',size_kb = ((dbsize + logsize) * 8192 / 1024),space_available_kb = (case when dbsize >= reservedpages then ((dbsize-reservedpages)* 8192/ 1024) else 0 end),reserved_kb = (reservedpages * 8192 / 1024),data_kb = (pages * 8192.0 / 1024),index_kb = ((usedpages - pages)  * 8192 / 1024),unused_kb = ((reservedpages - usedpages)  * 8192 / 1024)from(	select 	dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))  	from ['+@name+'].dbo.sysfiles) t1,(	select 	reservedpages = sum(a.total_pages),usedpages = sum(a.used_pages),pages = sum(  		CASE  		 When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0  		 When a.type <> 1 Then a.used_pages  		 When p.index_ID < 2 Then a.data_pages Else 0  		END )  	from ['+@name+'].sys.partitions p 	inner join ['+@name+'].sys.allocation_units a on p.partition_ID = a.container_ID  	left  join ['+@name+'].sys.internal_tables it on p.object_ID = it.object_ID  )t2'exec(@sql)fetch next from cur into @database_ID,@nameendclose curdeallocate curselect * from #dbsizeendgo

exec master.dbo.proc_getdbspaceused

数据库备份信息：

--	drop table #dbsizecreate table #dbsize(	 database_ID int,unused_kb bigint)goinsert into #dbsize exec master.dbo.proc_getdbspaceusedgoselect t0.database_ID,t0.database_name,t0.size_kb/1024 as 数据库大小MB,t0.space_available_kb/1024 as 可用空间MB,t1.compatibility_level as 兼容级别,t1.collation_name as 校对规则,t1.recovery_model_desc as 恢复模式,(case t3.type/*btype*/ when 'D' then '完整' when 'I' then '差异' when 'L' then '日志' end)as 备份模式,最近备份时间,备份大小MB,备份耗时Min,备份目录from #dbsize t0inner join master.sys.databases t1 on t0.database_name=t1.name--left JOIN (select btype from (values('D'),('I'),('L')) as btype(btype)) t2 on 1=1left join (	select a.database_name,a.type,CONVERT(varchar(30),a.backup_start_date,120) AS 最近备份时间,convert(decimal(18,2),compressed_backup_size/1024/1024.)  as 备份大小MB,DATEDIFF(MINUTE,backup_start_date,backup_finish_date) as 备份耗时Min,REVERSE(stuff(REVERSE(physical_device_name),1,CHARINDEX('\',REVERSE(physical_device_name))-1,'')) as 备份目录	from msdb.dbo.backupset a	inner join (		select database_name,type,MAX(backup_start_date) as latest_backup_date		from msdb.dbo.backupset		group by database_name,type	)b on a.database_name=b.database_name and a.type=b.type and a.backup_start_date=b.latest_backup_date	left join msdb.dbo.backupmediafamily c on a.media_set_ID=c.media_set_ID)t3 on t0.database_name=t3.database_name --and t2.btype=t3.typego

总结

以上是内存溢出为你收集整理的SQLServer 维护脚本分享（12）查看数据库空间分配情况（准确）全部内容，希望文章能够帮你解决SQLServer 维护脚本分享（12）查看数据库空间分配情况（准确）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。