SQLServer数据库状态监控-可用空间

数据库⽤来存放数据，那么肯定需要存储空间，所以对磁盘空间的监视⾃然就很有必要了。

⼀. 磁盘可⽤空间

1. 操作系统命令或脚本、接⼝或⼯具(1) DOS命令: fsutil volume diskfree

C:\\windows\\system32>fsutil volume diskfree C:Total # of free bytes : 97493248Total # of bytes : 424505344Total # of avail free bytes : 97493248

这⾥⽤到了fsutil，⼀个⽂件系统管理⼯具(file system utility)，应该还有其他⼀些命令或者脚本也是可以的。

(2) WMI/WMIC: wmic logicaldisk

WMI是个Windows系统的管理接⼝，在WMIC出现之前，如果要利⽤WMI管理系统，必须使⽤⼀些专门的WMI应⽤，例如SMS，或者使⽤WMI的脚本编程API，或者使⽤象CIMStudio之类的⼯具。如果不熟悉C++之类的编程语⾔或VBScript之类的脚本语⾔，或者不掌握WMI名称空间的基本知识，要⽤WMI管理系统是很困难的。WMIC改变了这种情况，它为WMI名称空间提供了⼀个强⼤的、友好的命令⾏接⼝。C:\\windows\\system32>wmic logicaldisk get caption,freespace,sizeCaption FreeSpace SizeC: 97071360 424505344D: 10134384 255331397632

这⾥通过wmic的get命令获取了logicaldisk 的⼏个参数列。

(3) 性能监视器

LogicalDisk: %Free SpaceLogicalDisk: Free Megabytes

总⼤⼩ = LogicalDisk: Free Megabytes/ LogicalDisk: %Free Space

性能监视器虽然⽤于现场诊断还是挺⽅便的，但实现⾃动化监控，并不太好⽤。

2. SQL 语句

(1) 扩展存储过程xp_cmdshell (还是在调⽤操作系统命令)

DECLARE @Drive TINYINT, @SQL VARCHAR(100)DECLARE @Drives TABLE(

Drive CHAR(1),Info VARCHAR(80))

SET @Drive = 97

WHILE @Drive <= 122BEGIN

SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':''' INSERT @Drives ( Info )

EXEC(@SQL)

UPDATE @Drives

SET Drive = CHAR(@Drive) WHERE Drive IS NULL SET @Drive = @Drive + 1END

SELECT Drive,

SUM(CASE WHEN Info LIKE 'Total # of bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS TotalMBytes,SUM(CASE WHEN Info LIKE 'Total # of free bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS FreeMBytes,

SUM(CASE WHEN Info LIKE 'Total # of avail free bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS AvailFreeMBytesFROM(

SELECT Drive, Info

FROM @Drives

WHERE Info LIKE 'Total # of %') AS d

GROUP BY DriveORDER BY Drive

xp_cmdshell可以执⾏操作系统命令⾏，这段脚本⽤fsutil volume diskfree命令对26个字母的盘符遍历了⼀遍，不是很好，改⽤wmic会⽅便些，如下：

EXEC xp_cmdshell 'wmic logicaldisk get caption,freespace,size';

(2) 扩展存储过程xp_fixeddrives

--exec xp_fixeddrives

IF object_id('tempdb..#drivefreespace') IS NOT NULL DROP TABLE #drivefreespace

CREATE TABLE #drivefreespace(Drive CHAR(1), FreeMb bigint)INSERT #drivefreespace EXEC ('exec xp_fixeddrives')SELECT * FROM #drivefreespace

总算不依赖操作系统命令了，不过，这个存储过程只能返回磁盘可⽤空间，没有磁盘总空间。

(3) DMV/DMF: sys.dm_os_volume_stats

SELECT DISTINCT

@@SERVERNAME as [server] ,volume_mount_point as drive

,cast(available_bytes/ 1024.0 / 1024.0 / 1024.0 AS INT) as free_gb ,cast(total_bytes / 1024.0 / 1024.0 / 1024.0 AS INT) as total_gbFROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)ORDER BY @@SERVERNAME, volume_mount_point

从SQL Server 2008 R2 SP1开始，有了这个很好⽤的DMF: sys.dm_os_volume_stats，弥补了之前xp_fixeddrives没有磁盘总空间的不⾜。不过，看它的参数就可以知道，没被任何数据库使⽤的磁盘，是查看不了的，所以xp_fixeddrives还有存在的必要。

⼆. 数据库可⽤空间1. ⽂件可⽤空间查看

(1) ⽂件已⽤空间，当前⼤⼩(已分配空间)，最⼤值，如下：

select @@SERVERNAME as server_name ,DB_NAME() as database_name

,case when data_space_id = 0 then 'LOG'

else FILEGROUP_NAME(data_space_id) end as file_group ,name as logical_name ,physical_name ,type_desc

,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_Mb ,size/128.0 as allocated_size_mb

,case when max_size = -1 then max_size else max_size/128.0 end as max_size_Mb ,growth

,is_percent_growth from sys.database_files

where state_desc = 'ONLINE'

(2) 再算上磁盘的空闲空间，改动如下：

select @@SERVERNAME as server_name ,DB_NAME() as database_name

,case when data_space_id = 0 then 'LOG'

else FILEGROUP_NAME(data_space_id) end as file_group ,name as logical_name ,physical_name ,type_desc

,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_mb ,size/128.0 as allocated_size_mb

,case when max_size = -1 then max_size else max_size/128.0 end as max_size_mb

,vs.available_bytes/1024.0/1024 as disk_free_mb ,growth

,CAST(is_percent_growth as int) as is_percent_growthfrom sys.database_files df

cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vswhere state_desc = 'ONLINE'

如果是SQL Server 2008 SP1以前的版本，可⽤xp_fixeddrives⽣成磁盘空闲空间表，再进⾏关联。

(3) 结合⽂件是否⾃增长，⽂件最⼤值，磁盘空间，算出⽂件可⽤空间⽐率，改动如下：

select @@SERVERNAME as server_name ,DB_NAME() as database_name

,case when data_space_id = 0 then 'LOG'

else FILEGROUP_NAME(data_space_id) end as file_group ,name as logical_name ,physical_name ,type_desc

,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_mb ,size/128.0 as allocated_size_mb

,case when max_size = -1 then max_size else max_size/128.0 end as max_size_mb

,vs.available_bytes/1024.0/1024 as disk_free_mb

,case when growth = 0 then (size - FILEPROPERTY(name,'SpaceUsed'))*1.0/size

when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)

when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/ when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) < 0 then (max_size - FILEPROPERTY(name,'SpaceUsed'))*1.0/max_size else null

end as free_space_percent ,growth

,CAST(is_percent_growth as int) as is_percent_growthfrom sys.database_files df

cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vswhere state_desc = 'ONLINE'

(4) 如果有多个数据库，注意fileproperty()和filegroup_name()函数，都只在当前数据库下⽣效，改动如下：

if object_id('tempdb..#tmp_filesize') is not null drop table #tmp_filesizeGO

create table #tmp_filesize(

server_name varchar(256),database_name varchar(256),file_group varchar(256),logical_name varchar(256),physical_name varchar(1024),type_desc varchar(128),used_size_mb float,allocated_size_mb float,max_size_mb float,disk_free_mb float,free_space_percent float,growth int,is_percent_growth int)

GO

exec sp_msforeachdb 'use [?] insert into #tmp_filesize

select @@SERVERNAME as server_name ,DB_NAME() as database_name

,case when data_space_id = 0 then ''LOG'' else FILEGROUP_NAME(data_space_id) end as file_group ,name as logical_name ,physical_name ,type_desc

,FILEPROPERTY(name,''SpaceUsed'')/128.0 as used_size_mb ,size/128.0 as allocated_size_mb

,case when max_size = -1 then max_size else max_size/128.0 end as max_size_mb

,vs.available_bytes/1024.0/1024 as disk_free_mb

,case when growth = 0 then (size - FILEPROPERTY(name,''SpaceUsed''))*1.0/size

when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)

when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/ when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) < 0 then (max_size - FILEPROPERTY(name,''SpaceUsed''))*1.0/max_size else null

end as free_space_percent ,growth

,CAST(is_percent_growth as int) as is_percent_growthfrom sys.database_files df

cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vswhere state_desc = ''ONLINE'''select * from #tmp_filesize

2. 数据库可⽤空间告警2.1 告警的格式

数据库可⽤空间告警，通常不告警某个⽂件，也不告警整个数据库，⽽是某个确切的⽂件组/表空间，⽇志⽂件是没有⽂件组的，所有可以把⽇志⽂件合并为LOG这个组。(1) Oracle可以给表空间设置最⼤尺⼨，表空间⾥的每个⽂件逐个使⽤，直到最后⼀个⽂件也没空间时，就会提⽰空间不⾜；

(2) SQL Server ⽆法对⽂件组设置最⼤尺⼨，只可以给⽂件组⾥每个⽂件指定最⼤尺⼨，所以要先统计：是否当前⽂件组下所有的⽂件都已经满了？将同⼀个⽂件组/LOG下的所有⽂件都检查⼀下，如果所有⽂件都满了(以20%为例)，那么就满⾜告警条件了，如下：

--#tmp_filesize 在上⾯的脚本⾥⽣成了select server_name, database_name, file_group,

MAX(free_space_percent) as max_free_space_percent from #tmp_filesize

group by server_name,database_name,file_group having MAX(free_space_percent) <= 0.2 --20%

邮件告警的格式⼤致为：

邮件标题：主机名\\实例名\\数据库名\\⽂件组名，@@servername已经包含了SQL Server实例名；邮件内容：⽂件组 ”file group name” 空间不⾜，已低于20%。

2.2 告警后如何处理？

(1) 告警中的⽂件组⾥的⽂件，所在的磁盘还有空间吗？

exec xp_fixeddrives

如果当前磁盘没空间，可以给当前⽂件组在其他磁盘上添加新的⽂件，并关闭⽼的⽂件⾃增长或最⼤值；

如果所有磁盘都没空间，可以考虑删除磁盘上的其他⽂件，或者收缩数据库⽂件(数据/⽇志)，或者磁盘扩展空间(加磁盘)。

(2) 如果磁盘有空间，⽂件是否关闭了⾃动增长？

可能是在创建⽂件时，给了⽂件⽐较⼤的size，如500G，并关闭了⽂件⾃动增长；

ALTER DATABASE testADD FILE (

NAME = test_02,

FILENAME = 'D:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\est_02.ndf', SIZE = 500 GB, FILEGROWTH = 0)

TO FILEGROUP [PRIMARY];GO

(3) 如果磁盘有空间，⾃动增长也开了，是不是了⽂件最⼤值？

最⼤值和关闭⾃增长，应该都是不想单个⽂件变得太⼤，个⼈觉得⼀个⽂件控制在500G以内⽐较合理，这两种情况，都建议扩展⼀个新⽂件。 ⼩结

如果没有监控⼯具，那么可选择系统视图，扩展存储过程，结合数据库邮件的⽅式，作⾃动检查，并告警⽂件组/⽇志空闲空间不⾜。⼤致步骤如下 ：(1) 部署数据库邮件；

(2) 部署作业：定时检查⽂件组/⽇志空闲空间，发邮件告警。