mssql 监控磁盘空间告警实现方法

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了mssql 监控磁盘空间告警实现方法，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

这几天突然有个想法：希望能够自动监控、收集数据库服务器的磁盘容量信息，当达到一个阀值后，自动发送告警邮件给DBA，将数据库磁盘详细信息告知DBA，提醒DBA做好存储规划计划，初步的想法是通过作业调用存储过程来实现（每天调用一次），这样避免了我每天每台数据库服务器都上去检查一下，尤其是手头的数据库服务器N多的情况，这样可以避免我每天浪费无谓的时间。如果大家有更好的建议和方法，欢迎指点一二，我整理、修改了三个存储过程如下：

存储过程1：SP_DiskCapacityAlert1.prc

说明：需要通过调用OLE 自动存储过程获取磁盘信息，而这些组件，基于服务器的安全配置，通常是禁用的，我们在存储过程通过sp_configure开启这个服务，调用服务完毕后，又通过sp_configure禁用该服务。另外，数据库服务器都位于内网，因此安全问题应该不大。

代码如下:
USE master;
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_diskcapacity_alert1') AND OBJECTPROPERTY(id,'IsProcedure') =1)
DROP PROCEDURE sp_diskcapacity_alert1;
GO--==================================================================================================================
-- ProcedureName : sp_diskcapacity_alert1
-- Author : Kerry
-- CreateDate : 2013-05-02
-- Description : 获取数据库所在服务器的磁盘容量，当达到阀值是，发送告警邮件，提醒DBA做好存储规划计划
/******************************************************************************************************************
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 修改HTML输出样式.以及磁盘容量输出改为GB
*******************************************************************************************************************/
--==================================================================================================================
CREATE PROCEDURE [dbo].[sp_diskcapacity_alert1]
(
@Threshold NUMERIC
)
ASSET NOCOUNT ON
DECLARE @Result INT;
DECLARE @objectInfo INT;
DECLARE @DriveInfo CHAR(1);
DECLARE @TotalSize VARCHAR(20);
DECLARE @OutDrive INT;
DECLARE @UnitMB BIGINT;
DECLARE @HtmlContent NVARCHAR(MAX) ;
DECLARE @FreeRat NUMERIC;
DECLARE @EmailHead VARCHAR(120);
SET @UnitMB = 1048576;
--创建临时表保存服务器磁盘容量信息
CREATE TABLE #DiskCapacity
(
[DiskCD] CHAR(1),
FreeSize INT,
TotalSize INT
);INSERT #DiskCapacity
([DiskCD],FreeSize )
EXEC master.dbo.xp_fixeddrives;EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'Ole Automation Procedures',1;
RECONFIGURE WITH OVERRIDE;
EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject',@objectInfo OUT;DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD
FOR SELECT DiskCD FROM #DiskCapacity
ORDER by DiskCDOPEN CR_DiskInfo;FETCH NEXT FROM CR_DiskInfo INTO @DriveInfoWHILE @@FETCH_STATUS=0
BEGIN EXEC @Result = sp_OAMethod @objectInfo,'GetDrive',@OutDrive OUT,@DriveInfo
EXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize',@TotalSize OUT
UPDATE #DiskCapacity
SET TotalSize=@TotalSize/@UnitMB
WHERE DiskCD=@DriveInfo FETCH NEXT FROM CR_DiskInfo INTO @DriveInfoENDCLOSE CR_DiskInfo
DEALLOCATE CR_DiskInfo;EXEC @Result=sp_OADestroy @objectInfoEXEC sp_configure 'show advanced options',0;
RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE;SELECT @FreeRat =FreeRate
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY FreeSize / ( TotalSize * 1.0 ) ASC) AS RowIndex,
CAST(( FreeSize / ( TotalSize * 1.0 ) ) * 100.0 AS INT) AS FreeRate FROM #DiskCapacity
) T
WHERE RowIndex = 1; IF @FreeRat <= @Threshold
BEGIN IF @FreeRat > 10 AND @FreeRat <=20
SET @EmailHead ='数据库磁盘容量告警(告警级别3)'
ELSE IF @FreeRat >=5 AND @FreeRat <=10
SET @EmailHead ='数据库磁盘容量告警(告警级别4)'
ELSE
SET @EmailHead ='数据库磁盘容量告警(告警级别5)' SET @HtmlContent =
+ N''
+ N''
+ N'

'
+ N''
+ N'磁盘盘符'
+ N'' +
CAST ( ( SELECT
td = DiskCD,'',
td = STR(TotalSize*1.0/1024,6,2),
td = STR((TotalSize - FreeSize)*1.0/1024,
td = STR(FreeSize*1.0/1024,
td = STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,
td = STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,''
FROM #DiskCapacity
FOR XML PATH('tr'),TYPE ) AS NVARCHAR(MAX) ) + N'' ; EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DataBase_DDL_Event',--指定你自己的profile_name
@recipients='****@163.com',--指定你要发送到的邮箱
@subject = '服务器磁盘空间告警',
@body = @HtmlContent,
@body_format = 'HTML' ;
END
DROP TABLE #DiskCapacity;RETURN;GO

存储过程2：SP_DiskCapacityAlert2.prc

说明：需要启用xp_cmdshell来获取磁盘信息，关于xp_cmdshell安全隐患，一般该功能都是禁用的。

代码如下:
USE [master]
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOIF OBJECT_ID(N'dbo.sp_diskcapacity_alert2') IS NOT NULL
DROP PROCEDURE dbo.sp_diskcapacity_alert2;GO--==================================================================================================================
-- ProcedureName : sp_diskcapacity_alert2
-- Author : Kerry
-- CreateDate : 2013-05-02
-- Description : 获取数据库所在服务器的磁盘容量，当达到阀值时，发送告警邮件，提醒DBA做好存储规划计划
/******************************************************************************************************************
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 修改HTML输出样式.以及磁盘容量输出改为GB
*******************************************************************************************************************/
--==================================================================================================================
CREATE PROCEDURE [dbo].[sp_diskcapacity_alert2]
(
@Threshold NUMERIC
)AS
BEGINSET NOCOUNT ON;DECLARE @HtmlContent NVARCHAR(MAX) ;
DECLARE @FreeRat NUMERIC;
DECLARE @EmailHead VARCHAR(200);--创建临时表保存服务器磁盘容量信息
CREATE TABLE #DiskCapacity
(
DiskCD CHAR(4),
FreeSize 　INT,
TotalSize BIGINT
);INSERT INTO #DiskCapacity
( DiskCD,FreeSize )
EXEC master..xp_fixeddrives;EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE
EXEC sp_configure 'show advanced options',0
RECONFIGURE
CREATE TABLE #DriveInfo1(ID INT IDENTITY(1,1),DiskCD VARCHAR(12));INSERT INTO #DriveInfo1(DiskCD)
EXEC xp_cmdshell 'wmic LOGICALDISK get name';
CREATE TABLE #DriveInfo2(ID INT IDENTITY(1,TotalSize VARCHAR(22));INSERT INTO #DriveInfo2
( TotalSize )
EXEC xp_cmdshell 'wmic LOGICALDISK get size';
DELETE FROM #DriveInfo1 WHERE ID=1;
DELETE FROM #DriveInfo2 WHERE ID=1;
UPDATE #DriveInfo1 SET DiskCD = REPLACE(DiskCD,':','');
SELECT * FROM #DiskCapacityUPDATE #DiskCapacity SET TotalSize =(SELECT CAST(LEFT(N.TotalSize,LEN(N.TotalSize)-1) AS BIGINT)/1024/1024 FROM #DriveInfo1 M INNER JOIN #DriveInfo2 N ON M.ID = N.ID
WHERE M.DiskCD IS NOT NULL AND LEN(M.DiskCD) >1 AND #DiskCapacity.DiskCD = LEFT(M.DiskCD,LEN(M.DiskCD)-1)) SELECT * FROM #DiskCapacityEXEC sp_configure 'show advanced options',0
RECONFIGURE
EXEC sp_configure 'show advanced options',0
RECONFIGURESELECT @FreeRat =FreeRate
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY FreeSize / ( TotalSize * 1.0 ) ASC) AS RowIndex,--指定你自己的profile_name
@recipients='konglb@***.com',
@body_format = 'HTML' ;
END
END
GO

存储过程3：SP_DiskCapacityAlert3.prc

说明：这个存储过程不用上面两个有安全隐患的存储过程，但是获取不到磁盘的总体信息，就不能通过一个阀值来告警，只能设置当磁盘剩余多少空间时，产生告警邮件。

代码如下:
USE [master]
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOIF OBJECT_ID(N'dbo.sp_diskcapacity_alert3') IS NOT NULL
DROP PROCEDURE dbo.sp_diskcapacity_alert3;GO--==================================================================================================================
-- ProcedureName : sp_diskcapacity_alert3
-- Author : Kerry
-- CreateDate : 2013-05-02
-- Description : 获取数据库所在服务器的磁盘容量，当某个磁盘剩余容量低于某个值时，发送告警邮件，
-- 提醒DBA做好存储规划计划
/******************************************************************************************************************
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 修改HTML输出样式.以及磁盘容量输出改为GB
*******************************************************************************************************************/
--==================================================================================================================
CREATE PROCEDURE [dbo].[sp_diskcapacity_alert3]
(
@DiskCapacity FLOAT
)AS
BEGINDECLARE @FreeSize INT;
DECLARE @EmailHead VARCHAR(200);
DECLARE @HtmlContent NVARCHAR(MAX) ;
--创建临时表保存服务器磁盘容量信息
CREATE TABLE #DiskCapacity
(
DiskCD CHAR(4),
FreeSize INT
);INSERT INTO #DiskCapacity
( DiskCD,FreeSize )
EXEC master..xp_fixeddrives;SELECT @FreeSize = FreeSize*1.0/1024
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY FreeSize ASC ) AS RowIndex,
FreeSize AS FreeSize
FROM #DiskCapacity
) T
WHERE RowIndex = 1 ;SELECT FreeSize*1.0/1024 FROM #DiskCapacity;
IF @FreeSize <= @DiskCapacity
BEGIN IF @FreeSize > 1
AND @FreeSize <= 2
SET @EmailHead = '数据库磁盘容量告警(告警级别3)'
ELSE
IF @FreeSize >= 0.5
AND @FreeSize <= 1
SET @EmailHead = '数据库磁盘容量告警(告警级别4)'
ELSE
SET @EmailHead = '数据库磁盘容量告警(告警级别5)' SET @HtmlContent = +N'' + N''
+ N'

+ @EmailHead + '

' + N'
总大小(GB)已用空间(GB)剩余空间(GB)已用比例(%)剩余比例(%)
'
+ N'磁盘盘符' + N''
+ CAST(( SELECT td = DiskCD,
'',
td = STR(FreeSize * 1.0 / 1024,
''
FROM #DiskCapacity
FOR
XML PATH('tr'),
TYPE
) AS NVARCHAR(MAX)) + N'' ; EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DataBase_DDL_Event',
@body_format = 'HTML' ; ENDEND
GO

作者：潇湘隐者
出处：http://www.cnblogs.com/kerrycode/

sqlserversqlserver告警监控监控监控磁盘空间

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问
剩余空间(GB)