3. SQL Server数据库状态监控 - 可用空间
数据库用来存放数据,那么肯定需要存储空间,所以对磁盘空间的监视自然就很有必要了。
一. 磁盘可用空间
1. 操作系统命令或脚本、接口或工具
(1) DOS命令: fsutil volume diskfree
C:\windows\system32>fsutil volume diskfree C:
Total # of free bytes : 9789493248
Total # of bytes : 64424505344
Total # of avail free bytes : 9789493248
这里用到了fsutil,一个文件系统管理工具(file system utility),应该还有其他一些命令或者脚本也是可以的。
(2) WMI/WMIC: wmic logicaldisk
WMI是个Windows系统的管理接口,在WMIC出现之前,如果要利用WMI管理系统,必须使用一些专门的WMI应用,例如SMS,或者使用WMI的脚本编程API,或者使用象CIM Studio之类的工具。如果不熟悉C++之类的编程语言或VBScript之类的脚本语言,或者不掌握WMI名称空间的基本知识,要用WMI管理系统是很困难的。WMIC改变了这种情况,它为WMI名称空间提供了一个强大的、友好的命令行接口。
C:\windows\system32>wmic logicaldisk get caption,freespace,size
Caption FreeSpace Size
C: 9789071360 64424505344
D: 189013438464 255331397632
这里通过wmic的get命令获取了logicaldisk 的几个参数列。
(3) 性能监视器
LogicalDisk: %Free Space
LogicalDisk: 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 <= 122 BEGIN 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 + 1 END 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 AvailFreeMBytes FROM( SELECT Drive, Info FROM @Drives WHERE Info LIKE 'Total # of %' ) AS d GROUP BY Drive ORDER 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
Drive |
FreeMb |
C |
9316 |
D |
180013 |
总算不依赖操作系统命令了,不过,这个存储过程只能返回磁盘可用空间,没有磁盘总空间。
(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_gb FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) ORDER BY @@SERVERNAME, volume_mount_point
server |
drive |
free_gb |
total_gb |
… |
C:\ |
9 |
59 |
… |
D:\ |
175 |
237 |
从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_growth from sys.database_files df cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs where 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/1024.0/1024) 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_growth from sys.database_files df cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs where state_desc = 'ONLINE'
(4) 如果有多个数据库,注意fileproperty()和filegroup_name()函数,都只在当前数据库下生效,改动如下:
if object_id('tempdb..#tmp_filesize') is not null drop table #tmp_filesize GO 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/1024.0/1024) 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_growth from sys.database_files df cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs where 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 test ADD FILE ( NAME = test_02, FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_02.ndf', SIZE = 500 GB, FILEGROWTH = 0 ) TO FILEGROUP [PRIMARY]; GO
(3) 如果磁盘有空间,自动增长也开了,是不是限制了文件最大值?
限制最大值和关闭自增长,应该都是不想单个文件变得太大,个人觉得一个文件控制在500G以内比较合理,这两种情况,都建议扩展一个新文件。
小结
如果没有监控工具,那么可选择系统视图,扩展存储过程,结合数据库邮件的方式,作自动检查,并告警文件组/日志空闲空间不足。大致步骤如下 :
(1) 部署数据库邮件;
(2) 部署作业:定时检查文件组/日志空闲空间,发邮件告警。