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

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

时间： 2024-10-26 20:57:18

SQL Server数据库状态监控 - 可用空间的相关文章

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

原文:3. SQL Server数据库状态监控 - 可用空间 数据库用来存放数据,那么肯定需要存储空间,所以对磁盘空间的监视自然就很有必要了. 一. 磁盘可用空间 1. 操作系统命令或脚本.接口或工具 (1) DOS命令: fsutil volume diskfree C:\windows\system32>fsutil volume diskfree C: Total # of free bytes        : 9789493248 Total # of bytes           

2. SQL Server数据库状态监控 - 错误日志

原文:2. SQL Server数据库状态监控 - 错误日志 无论是操作系统 (Unix 或者Windows),还是应用程序 (Web 服务,数据库系统等等) ,通常都有自身的日志机制,以便故障时追溯现场及原因.Windows Event Log和 SQL Server Error Log就是这样的日志, PS: SQL Server 中的错误日志 (Error Log) 类似于 Oracle中的alert 文件. 一. 错误日志简介 1. Windows事件日志与SQL Server 错误日志

4. SQL Server数据库状态监控 - 作业状态

原文:4. SQL Server数据库状态监控 - 作业状态 有很多地方可以设置定时任务,比如:Windows的计划任务,Linux下的crontab,各种开发工具里的timer组件.SQL Server也有它的定时任务组件 SQL Server Agent,基于它可以方便的部署各种数据库相关的作业(job). 一. 作业历史纪录 作业的历史纪录按时间采用FIFO原则,当累积的作业历史纪录达到上限时,就会删除最老的纪录. 1. 作业历史纪录数配置 所有作业总计纪录条数默认为1000,最多为999

SQL Server数据库状态监控 - 错误日志

无论是操作系统 (Unix 或者Windows),还是应用程序 (Web 服务,数据库系统等等) ,通常都有自身的日志机制,以便故障时追溯现场及原因.Windows Event Log和 SQL Server Error Log就是这样的日志, PS: SQL Server 中的错误日志 (Error Log) 类似于 Oracle中的alert 文件. 一. 错误日志简介 1. Windows事件日志与SQL Server 错误日志 Windows事件日志中,应用程序里的SQL Server和

详解SQL Server数据库状态和文件状态

数据库状态 (database states) 查询数据库的当前状态 : 1.查询所有数据库的状态 ,通过sys.databases目录视图的state_desc列 user master go select state_desc ,[name] from sys.databases go 2.查询指定数据库的状态,通过DATABASEPROPERTYEX函数的Status属性 select DATABASEPROPERTYEX('demoData','status') go 状态: ONLIN

5. SQL Server数据库性能监控 - 当前请求

原文:5. SQL Server数据库性能监控 - 当前请求 对于在线运行的系统,当前数据库性能监控,通常监视以下几点: (1) 是否有阻塞 (Blocking); (2) 是否有等待 (Waiting),阻塞就是锁 (Lock) 等待; (3) 是否运行时间过长(Long running): (4) 是否有死锁 (Deadlock): sys.dm_exec_query_stats之类,等一些统计性的信息,通常不作为实时告警内容,而是在性能优化时,作为参考.   一. 阻塞/等待/长时间运行

sql server 数据库操作报错“日志不可用”

问题描述 sql server 数据库操作报错"日志不可用" 在操作数据库时查询信息操作没有问题,但增删改操作时就会报下面的错误请问是什么原因: 消息 9001,级别 21,状态 1,第 2 行 数据库 'hrtest1' 的日志不可用.有关相应错误消息,请查看事件日志.修复所有错误后重新启动数据库. 解决方案 SQL Server 数据库操作关于SQL Server数据库日志已满的解决方法SQL Server 数据库操作总结(sql语法的使用) 解决方案二: recovery下数据库

利用SQL Server数据库邮件服务实现监控和预警_MsSql

背景 现在越来越多的企业.公司要求对于数据库实现7*24小时的数据库监控,一般情况下采用的就是第三方的平台来实现邮件和手机短信的监测提醒.前几日公司新上了一台服务器,急于部署程序还没来得及搭建其他相关平台,为了更好的监控数据库,暂时用SQL Server自带的邮件服务来实现对数据库的监控和预警.下面简要介绍下配置的过程便于以后使用. 配置邮件 整个部分的核心就是配置邮件服务,这部分需要一个邮件账户以及相应的邮件服务器.下面就以QQ的邮件为例进行说明. 1.设置邮件服务器 开启SMTP服务,点击开

6. SQL Server数据库监控 - 如何告警

原文:6. SQL Server数据库监控 - 如何告警 常用的告警方式大致有:短信.邮件.应用程序 (beep提示,图标提示,升窗提示等),可是不能一直坐在电脑前看着应用程序,或者用脚本部署监控,根本没有程序界面,所以通常用短信.邮件两种方式告警. 一. 告警方式 1. 短信 用程序发短信的方式一般有这两种: (1) 硬件 需要1张SIM卡,1个SIM卡读卡设备 (比如:短信猫),然后把设备连接到电脑,应用程序根据设备的软件接口,传参并发送短信.记得把SIM卡设备放在信号好,无干扰的地方: 如