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/

原文链接:https://www.f2er.com/mssql/66128.html

猜你在找的MsSQL相关文章

剩余空间(GB)