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

　　一. 磁盘可用空间

　　(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

　　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

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

　　(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(

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

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) 再算上磁盘的空闲空间，改动如下：

,vs.available_bytes/1024.0/1024 as disk_free_mb

,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

　　如果是sql server 2008 sp1以前的版本，可用xp_fixeddrives生成磁盘空闲空间表，再进行关联。

　　(3) 结合文件是否自增长，文件最大值，磁盘空间，算出文件可用空间比率，改动如下：

,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

(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

exec sp_msforeachdb 'use [?]

insert into #tmp_filesize

,case when data_space_id = 0 then ''log''

,fileproperty(name,''spaceused'')/128.0 as used_size_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

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) 部署作业：定时检查文件组/日志空闲空间，发邮件告警。

最新内容请见作者的github页：http://qaseven.github.io/