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

use master
go
create procedure dbo.proc_getdbspaceused
as
begin
set nocount on
create 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 int
declare @name nvarchar(1024)
declare @sql nvarchar(max)
declare cur cursor for select database_id,name from master.sys.databases order by database_id
open cur
fetch next from cur into @database_id,@name
while @@fetch_status=0
begin
set @sql =N'
insert into #dbsize
select
	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,@name
end
close cur
deallocate cur

select * from #dbsize
end
go

exec master.dbo.proc_getdbspaceused

数据库备份信息：

--	drop table #dbsize
create table #dbsize(
	 database_id int,unused_kb bigint
)
go
insert into #dbsize exec master.dbo.proc_getdbspaceused
go
select 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 t0
inner 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=1
left 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.type
go

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!