qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

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

posted on 2014-09-19 13:23 顺其自然EVO 阅读(510) 评论(0)  编辑  收藏 所属分类: 测试学习专栏 、数据库


只有注册用户登录后才能发表评论。
网站导航:
 
<2014年9月>
日一二三四五六
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

导航

统计

常用链接

留言簿(55)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜