Home > Article > Database > 3.SQLServer数据库状态监控-可用空间

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

WBOYOriginal: 2016-06-07 14:52:002031browse

数据库用来存放数据，那么肯定需要存储空间，所以对磁盘空间的监视自然就很有必要了。一. 磁盘可用空间 1. 操作系统命令或脚本、接口或工具 (1) DOS命令: fsutil volume diskfree C:\windows\system32fsutil volume diskfree C: Total # of free bytes : 97

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

一. 磁盘可用空间

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

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：通过案例学调优之--动态视图（v$bh和x$bh)Next article：锦囊的使用方法_QQ三国

See more

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

Related articles