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

huiselele

　　数据库用来存放数据，那么肯定需要存储空间，所以对磁盘空间的监视自然就很有必要了。
　　一. 磁盘可用空间
　　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    >　　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  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)