首页 > 代码库 > MS SQL 监控磁盘空间告警

MS SQL 监控磁盘空间告警

        这几天突然有个想法:希望能够自动监控、收集数据库服务器的磁盘容量信息,当达到一个阀值后,自动发送告警邮件给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(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
)
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(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 
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_DiskCapacityAlert2.prc

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

USE [master]
GO
 
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
IF 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 
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

MS SQL 监控磁盘空间告警