程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL2008 >> mssql 監控磁盤空間告警完成辦法

mssql 監控磁盤空間告警完成辦法

編輯:MSSQL2008

mssql 監控磁盤空間告警完成辦法。本站提示廣大學習愛好者:(mssql 監控磁盤空間告警完成辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是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(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 >' + @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 >' + @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(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 >'
            + @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

作者:潇湘隱者
出處:http://www.cnblogs.com/kerrycode/

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved