mssql 监控磁盘空间告警实现方法 |
|
这几天突然有个想法：希望能够自动监控、收集数据库服务器的磁盘容量信息，当达到一个阀值后，自动发送告警邮件给DBA，将数据库磁盘详细信息告知DBA，提醒DBA做好存储规划计划，初步的想法是通过作业调用存储过程来实现（每天调用一次），这样避免了我每天每台数据库服务器都上去检查一下，尤其是手头的数据库服务器N多的情况，这样可以避免我每天浪费无谓的时间 。如果大家有更好的建议和方法，欢迎指点一二，我整理、修改了三个存储过程如下： 存储过程1：SP_DiskCapacityAlert1.prc 说明：需要通过调用OLE 自动存储过程获取磁盘信息，而这些组件，基于服务器的安全配置，通常是禁用的，我们在存储过程通过sp_configure开启这个服务，调用服务完毕后，又通过sp_configure禁用该服务 。另外，数据库服务器都位于内网，因此安全问题应该不大 。 复制代码 代码如下: USE master; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(Nsp_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 ) AS SET 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 DiskCD OPEN CR_DiskInfo; FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo WHILE @@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 @DriveInfo END CLOSE CR_DiskInfo DEALLOCATE CR_DiskInfo; EXEC @Result=sp_OADestroy @objectInfo EXEC sp_configure show advanced options, 1 RECONFIGURE WITH OVERRIDE; EXEC sp_configure Ole Automation Procedures, 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<html> + N<style type="text/css"> + N td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} + N table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} + N</style> + N<H1 style="color:#FF0000; text-align:center;font-size:14px"> + @EmailHead +</H1> + N<table > + N<tr><th>磁盘盘符</th><th>总大小(GB)</th><th>已用空间(GB)</th><th>剩余空间(GB)</th> + N<th>已用比例(%)</th><th>剩余比例(%)</th></tr > + CAST ( ( SELECT td = DiskCD , , td = STR(TotalSize*1.0/1024,6,2) , , td = STR((TotalSize - FreeSize)*1.0/1024,6,2) , , td = STR(FreeSize*1.0/1024,6,2) , , td = STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,6,2), , td = STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,6,2) , FROM #DiskCapacity FOR XML PATH(tr), TYPE ) AS NVARCHAR(MAX) ) + N</table></html> ; 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 GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID(Ndbo.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 BEGIN SET 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,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 #DiskCapacity UPDATE #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 #DiskCapacity EXEC sp_configure show advanced options, 1 RECONFIGURE EXEC sp_configure xp_cmdshell, 0 RECONFIGURE EXEC sp_configure show advanced options, 0 RECONFIGURE 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<html> + N<style type="text/css"> + N td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} + N table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} + N</style> + N<H1 style="color:#FF0000; text-align:center;font-size:14px"> + @EmailHead +</H1> + N<table > + N<tr><th>磁盘盘符</th><th>总大小(GB)</th><th>已用空间(GB)</th><th>剩余空间(GB)</th> + N<th>已用比例(%)</th><th>剩余比例(%)</th></tr > + CAST ( ( SELECT td = DiskCD , , td = STR(TotalSize*1.0/1024,6,2) , , td = STR((TotalSize - FreeSize)*1.0/1024,6,2) , , td = STR(FreeSize*1.0/1024,6,2) , , td = STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,6,2), , td = STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,6,2) , FROM #DiskCapacity FOR XML PATH(tr), TYPE ) AS NVARCHAR(MAX) ) + N</table></html> ; EXEC msdb.dbo.sp_send_dbmail @profile_name = DataBase_DDL_Event, --指定你自己的profile_name @recipients=konglb@***.com, --指定你要发送到的邮箱 @subject = 服务器磁盘空间告警, @body = @HtmlContent, @body_format = HTML ; END END GO 存储过程3：SP_DiskCapacityAlert3.prc 说明：这个存储过程不用上面两个有安全隐患的存储过程，但是获取不到磁盘的总体信息，就不能通过一个阀值来告警，只能设置当磁盘剩余多少空间时，产生告警邮件 。 复制代码 代码如下: USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID(Ndbo.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 BEGIN DECLARE @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<html> + N<style type="text/css"> + N td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} + N table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} + N</style> + N<H1 style="color:#FF0000; text-align:center;font-size:14px"> + @EmailHead + </H1> + N<table > + N<tr><th>磁盘盘符</th><th>剩余空间(GB)</th> + N</tr > + CAST(( SELECT td = DiskCD , , td = STR(FreeSize * 1.0 / 1024, 6, 2) , FROM #DiskCapacity FOR XML PATH(tr) , TYPE ) AS NVARCHAR(MAX)) + N</table></html> ; EXEC msdb.dbo.sp_send_dbmail @profile_name = DataBase_DDL_Event, --指定你自己的profile_name @recipients=konglb@***.com, --指定你要发送到的邮箱 @subject = 服务器磁盘空间告警, @body = @HtmlContent, @body_format = HTML ; END END GO 作者：潇湘隐者 |
