这几天突然有个想法:希望能够自动监控、收集数据库服务器的磁盘容量信息,当达到一个阀值后,自动发送告警邮件给DBA,将数据库磁盘详细信息告知DBA,提醒DBA做好存储规划计划,初步的想法是通过作业调用存储过程来实现(每天调用一次),这样避免了我每天每台数据库服务器都上去检查一下,尤其是手头的数据库服务器N多的情况,这样可以避免我每天浪费无谓的时间。如果大家有更好的建议和方法,欢迎指点一二,我整理、修改了三个存储过程如下:
存储过程1:SP_DiskCapacityAlert1.prc
说明:需要通过调用OLE 自动存储过程获取磁盘信息,而这些组件,基于服务器的安全配置,通常是禁用的,我们在存储过程通过sp_configure开启这个服务,调用服务完毕后,又通过sp_configure禁用该服务。另外,数据库服务器都位于内网,因此安全问题应该不大。
代码如下:
301_12@USE master;@H_301_12@GOSET ANSI_NULLS ON@H_301_12@GOSET QUOTED_IDENTIFIER ON@H_301_12@GOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_diskcapacity_alert1') AND OBJECTPROPERTY(id,'IsProcedure') =1)@H_301_12@ DROP PROCEDURE sp_diskcapacity_alert1;@H_301_12@GO--==================================================================================================================@H_301_12@-- ProcedureName : sp_diskcapacity_alert1@H_301_12@-- Author : Kerry @H_301_12@-- CreateDate : 2013-05-02@H_301_12@-- Description : 获取数据库所在服务器的磁盘容量,当达到阀值是,发送告警邮件,提醒DBA做好存储规划计划@H_301_12@/******************************************************************************************************************@H_301_12@ Modified Date Modified User Version Modified Reason@H_301_12@ 2013-05-6 Kerry V01.00.00 修改HTML输出样式.以及磁盘容量输出改为GB@H_301_12@*******************************************************************************************************************/@H_301_12@--==================================================================================================================@H_301_12@CREATE PROCEDURE [dbo].[sp_diskcapacity_alert1]@H_301_12@(@H_301_12@ @Threshold NUMERIC@H_301_12@)@H_301_12@ASSET NOCOUNT ON@H_301_12@DECLARE @Result INT;@H_301_12@DECLARE @objectInfo INT;@H_301_12@DECLARE @DriveInfo CHAR(1);@H_301_12@DECLARE @TotalSize VARCHAR(20);@H_301_12@DECLARE @OutDrive INT;@H_301_12@DECLARE @UnitMB BIGINT;@H_301_12@DECLARE @HtmlContent NVARCHAR(MAX) ; @H_301_12@DECLARE @FreeRat NUMERIC;@H_301_12@DECLARE @EmailHead VARCHAR(120);@H_301_12@SET @UnitMB = 1048576;@H_301_12@--创建临时表保存服务器磁盘容量信息@H_301_12@CREATE TABLE #DiskCapacity@H_301_12@(@H_301_12@ [DiskCD] CHAR(1),@H_301_12@ FreeSize INT,@H_301_12@ TotalSize INT @H_301_12@);INSERT #DiskCapacity@H_301_12@ ([DiskCD],FreeSize ) @H_301_12@EXEC master.dbo.xp_fixeddrives;EXEC sp_configure 'show advanced options',1@H_301_12@RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'Ole Automation Procedures',1;@H_301_12@RECONFIGURE WITH OVERRIDE;@H_301_12@EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject',@objectInfo OUT;DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD@H_301_12@FOR SELECT DiskCD FROM #DiskCapacity@H_301_12@ORDER by DiskCDOPEN CR_DiskInfo;FETCH NEXT FROM CR_DiskInfo INTO @DriveInfoWHILE @@FETCH_STATUS=0@H_301_12@BEGIN EXEC @Result = sp_OAMethod @objectInfo,'GetDrive',@OutDrive OUT,@DriveInfo@H_301_12@ EXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize',@TotalSize OUT@H_301_12@ UPDATE #DiskCapacity@H_301_12@ SET TotalSize=@TotalSize/@UnitMB@H_301_12@ WHERE DiskCD=@DriveInfo FETCH NEXT FROM CR_DiskInfo INTO @DriveInfoENDCLOSE CR_DiskInfo@H_301_12@DEALLOCATE CR_DiskInfo;EXEC @Result=sp_OADestroy @objectInfoEXEC sp_configure 'show advanced options',0;@H_301_12@RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'show advanced options',0@H_301_12@RECONFIGURE WITH OVERRIDE;SELECT @FreeRat =FreeRate@H_301_12@FROM (@H_301_12@ SELECT ROW_NUMBER() OVER (ORDER BY FreeSize / ( TotalSize * 1.0 ) ASC) AS RowIndex,@H_301_12@ CAST(( FreeSize / ( TotalSize * 1.0 ) ) * 100.0 AS INT) AS FreeRate FROM #DiskCapacity@H_301_12@ ) T@H_301_12@WHERE RowIndex = 1; IF @FreeRat <= @Threshold@H_301_12@ BEGIN IF @FreeRat > 10 AND @FreeRat <=20 @H_301_12@ SET @EmailHead ='数据库磁盘容量告警(告警级别3)'@H_301_12@ ELSE IF @FreeRat >=5 AND @FreeRat <=10@H_301_12@ SET @EmailHead ='数据库磁盘容量告警(告警级别4)'@H_301_12@ ELSE@H_301_12@ SET @EmailHead ='数据库磁盘容量告警(告警级别5)' SET @HtmlContent =@H_301_12@ + N''@H_301_12@ + N''@H_301_12@ + N'' @H_301_12@ + N'
' ; EXEC msdb.dbo.sp_send_dbmail @H_301_12@ @profile_name = 'DataBase_DDL_Event',--指定你自己的profile_name @H_301_12@ @recipients='****@163.com',--指定你要发送到的邮箱@H_301_12@ @subject = '服务器磁盘空间告警',@H_301_12@ @body = @HtmlContent,@H_301_12@ @body_format = 'HTML' ; @H_301_12@ END @H_301_12@DROP TABLE #DiskCapacity;RETURN;GO@H_301_12@
' @H_301_12@ + N'
总大小(GB) | 已用空间(GB) | 剩余空间(GB) | ' @H_301_12@ + N'已用比例(%) | 剩余比例(%) |
---|
存储过程2:SP_DiskCapacityAlert2.prc
说明:需要启用xp_cmdshell来获取磁盘信息,关于xp_cmdshell安全隐患,一般该功能都是禁用的。
代码如下:
301_12@USE [master]@H_301_12@GO@H_301_12@SET ANSI_NULLS ON@H_301_12@GOSET QUOTED_IDENTIFIER ON@H_301_12@GOIF OBJECT_ID(N'dbo.sp_diskcapacity_alert2') IS NOT NULL@H_301_12@ DROP PROCEDURE dbo.sp_diskcapacity_alert2;GO--==================================================================================================================@H_301_12@-- ProcedureName : sp_diskcapacity_alert2@H_301_12@-- Author : Kerry @H_301_12@-- CreateDate : 2013-05-02@H_301_12@-- Description : 获取数据库所在服务器的磁盘容量,当达到阀值时,发送告警邮件,提醒DBA做好存储规划计划@H_301_12@/******************************************************************************************************************@H_301_12@ Modified Date Modified User Version Modified Reason@H_301_12@ 2013-05-6 Kerry V01.00.00 修改HTML输出样式.以及磁盘容量输出改为GB@H_301_12@*******************************************************************************************************************/@H_301_12@--==================================================================================================================@H_301_12@CREATE PROCEDURE [dbo].[sp_diskcapacity_alert2]@H_301_12@(@H_301_12@ @Threshold NUMERIC@H_301_12@)AS @H_301_12@BEGINSET NOCOUNT ON;DECLARE @HtmlContent NVARCHAR(MAX) ; @H_301_12@DECLARE @FreeRat NUMERIC;@H_301_12@DECLARE @EmailHead VARCHAR(200);--创建临时表保存服务器磁盘容量信息@H_301_12@CREATE TABLE #DiskCapacity@H_301_12@(@H_301_12@ DiskCD CHAR(4),@H_301_12@ FreeSize INT,@H_301_12@ TotalSize BIGINT @H_301_12@);INSERT INTO #DiskCapacity@H_301_12@ ( DiskCD,FreeSize )@H_301_12@EXEC master..xp_fixeddrives;EXEC sp_configure 'show advanced options',1@H_301_12@RECONFIGURE@H_301_12@EXEC sp_configure 'xp_cmdshell',1@H_301_12@RECONFIGURE@H_301_12@EXEC sp_configure 'show advanced options',0@H_301_12@RECONFIGURE@H_301_12@CREATE TABLE #DriveInfo1(ID INT IDENTITY(1,1),DiskCD VARCHAR(12));INSERT INTO #DriveInfo1(DiskCD)@H_301_12@EXEC xp_cmdshell 'wmic LOGICALDISK get name';@H_301_12@CREATE TABLE #DriveInfo2(ID INT IDENTITY(1,TotalSize VARCHAR(22));INSERT INTO #DriveInfo2@H_301_12@ ( TotalSize )@H_301_12@EXEC xp_cmdshell 'wmic LOGICALDISK get size'; @H_301_12@DELETE FROM #DriveInfo1 WHERE ID=1;@H_301_12@DELETE FROM #DriveInfo2 WHERE ID=1; @H_301_12@UPDATE #DriveInfo1 SET DiskCD = REPLACE(DiskCD,':','');@H_301_12@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@H_301_12@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@H_301_12@RECONFIGURE@H_301_12@EXEC sp_configure 'show advanced options',0@H_301_12@RECONFIGURESELECT @FreeRat =FreeRate@H_301_12@FROM (@H_301_12@ SELECT ROW_NUMBER() OVER (ORDER BY FreeSize / ( TotalSize * 1.0 ) ASC) AS RowIndex,--指定你自己的profile_name @H_301_12@ @recipients='konglb@***.com',@H_301_12@ @body_format = 'HTML' ; @H_301_12@ END @H_301_12@END @H_301_12@GO@H_301_12@
存储过程3:SP_DiskCapacityAlert3.prc
说明:这个存储过程不用上面两个有安全隐患的存储过程,但是获取不到磁盘的总体信息,就不能通过一个阀值来告警,只能设置当磁盘剩余多少空间时,产生告警邮件。
代码如下:
301_12@USE [master]@H_301_12@GO@H_301_12@SET ANSI_NULLS ON@H_301_12@GOSET QUOTED_IDENTIFIER ON@H_301_12@GOIF OBJECT_ID(N'dbo.sp_diskcapacity_alert3') IS NOT NULL@H_301_12@ DROP PROCEDURE dbo.sp_diskcapacity_alert3;GO--==================================================================================================================@H_301_12@-- ProcedureName : sp_diskcapacity_alert3@H_301_12@-- Author : Kerry @H_301_12@-- CreateDate : 2013-05-02@H_301_12@-- Description : 获取数据库所在服务器的磁盘容量,当某个磁盘剩余容量低于某个值时,发送告警邮件,@H_301_12@-- 提醒DBA做好存储规划计划@H_301_12@/******************************************************************************************************************@H_301_12@ Modified Date Modified User Version Modified Reason@H_301_12@ 2013-05-6 Kerry V01.00.00 修改HTML输出样式.以及磁盘容量输出改为GB@H_301_12@*******************************************************************************************************************/@H_301_12@--==================================================================================================================@H_301_12@CREATE PROCEDURE [dbo].[sp_diskcapacity_alert3]@H_301_12@(@H_301_12@ @DiskCapacity FLOAT@H_301_12@)AS @H_301_12@BEGINDECLARE @FreeSize INT;@H_301_12@DECLARE @EmailHead VARCHAR(200);@H_301_12@DECLARE @HtmlContent NVARCHAR(MAX) ; @H_301_12@--创建临时表保存服务器磁盘容量信息@H_301_12@CREATE TABLE #DiskCapacity@H_301_12@(@H_301_12@ DiskCD CHAR(4),@H_301_12@ FreeSize INT @H_301_12@);INSERT INTO #DiskCapacity@H_301_12@ ( DiskCD,FreeSize )@H_301_12@EXEC master..xp_fixeddrives;SELECT @FreeSize = FreeSize*1.0/1024@H_301_12@FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY FreeSize ASC ) AS RowIndex,@H_301_12@ FreeSize AS FreeSize@H_301_12@ FROM #DiskCapacity@H_301_12@ ) T@H_301_12@WHERE RowIndex = 1 ;SELECT FreeSize*1.0/1024 FROM #DiskCapacity;@H_301_12@ IF @FreeSize <= @DiskCapacity @H_301_12@ BEGIN IF @FreeSize > 1@H_301_12@ AND @FreeSize <= 2 @H_301_12@ SET @EmailHead = '数据库磁盘容量告警(告警级别3)'@H_301_12@ ELSE @H_301_12@ IF @FreeSize >= 0.5@H_301_12@ AND @FreeSize <= 1 @H_301_12@ SET @EmailHead = '数据库磁盘容量告警(告警级别4)'@H_301_12@ ELSE @H_301_12@ SET @EmailHead = '数据库磁盘容量告警(告警级别5)' SET @HtmlContent = +N'' + N''@H_301_12@ + N''@H_301_12@ + N'
' ; EXEC msdb.dbo.sp_send_dbmail @H_301_12@ @profile_name = 'DataBase_DDL_Event',@H_301_12@ @body_format = 'HTML' ; ENDEND@H_301_12@GO@H_301_12@
301_12@ + @EmailHead + '
' + N'剩余空间(GB) | ' + N'
---|
作者:潇湘隐者@H_301_12@出处:http://www.cnblogs.com/kerrycode/