MS SQL Monitors disk space alarms

Source: Internet
Author: User
Tags dba ole

These days suddenly have an idea: want to be able to automatically monitor and collect the database server disk capacity information, when a threshold is reached, automatically send an alarm message to the DBA, the database disk details to the DBA, remind the DBA to do a storage planning plan, The initial idea is to call the stored procedure through the job (called Once a day), so as to avoid every day I each database server go up to check, especially the database server at hand more than N, so that I can avoid wasting unnecessary time every day. If you have better suggestions and methods, welcome to pointing Twos, I organized, modified the three stored procedures are as follows:

Stored Procedure 1:SP_DISKCAPACITYALERT1.PRC

Description: You need to obtain disk information by calling the OLE automatic stored procedure, which, based on the security configuration of the server, is usually disabled, we open the service through sp_configure in the stored procedure, and after the service is finished, the service is disabled through sp_configure. In addition, the database server is located in the intranet, so security issues should be small.

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, ' Isprocedur E ') =1)
    DROP PROCEDURE Sp_diskcapacity_alert1;
GO
--============================================================================================================= =====
--        procedurename        :            sp_diskcapacity_alert1
--        Author               :            Kerry    
--        createdate           :            2013-05-02
--        Description          :            Gets the disk capacity of the server on which the database resides, sends an alert message when the threshold is reached, alerts the DBA to a storage planning plan
/************************************************************************************************************** ****
    Modified Date        Modified User        Version                    Modified Reason
    2013-05-6               Kerry            V01.00.00          Modify the HTML output style. and disk capacity output to 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           
DECLARE @FreeRat               NUMERIC;
DECLARE @EmailHead             VARCHAR (120);
SET @UnitMB = 1048576;
--Create temporary table to save server disk capacity information
CREATE TABLE #DiskCapacity
(
    [DISKCD]        CHAR (1),
    Freesize        INT        ,
    TotalSize       INT        
);
INSERT #DiskCapacity
        
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 to @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 [Email protected]/@UnitMB
    WHERE [email protected]
    FETCH NEXT from Cr_diskinfo to @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
        
        
            SET @EmailHead = ' Database disk capacity alarm (Alarm level 3) '
        ELSE IF @FreeRat >=5 and @FreeRat <=10
            SET @EmailHead = ' Database disk capacity alarm (Alarm level 4) '
        ELSE
            SET @EmailHead = ' Database disk capacity alarm (Alarm level 5) '
        
        SET @HtmlContent =
            +   N ' 
            +   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 ' 

            +   N ' <table  > '    
            +   N ' <tr><th> disk drive letter </th><th> total size (GB) </th><th> used space (GB) </th><th > Remaining space (GB) </th> '     
            +   N ' <th> used ratio (%) </th><th> remaining ratio (%) </th></tr > ' +    
            
            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)) +     
            
         
            
         EXEC Msdb.dbo.sp_send_dbmail     
            @profile_name = ' database_ddl_event ',    --Specify your own profile_name    
            @recipients = ' ****@163.com ',                --Specify the mailbox you want to send to
            @subject = ' Server disk space alert ',     
            @body = @HtmlContent,   
            
        END
    
DROP TABLE #DiskCapacity;
RETURN;
GO

Stored Procedure 2:SP_DISKCAPACITYALERT2.PRC

Note: You need to enable xp_cmdshell to get disk information about xp_cmdshell security implications, which is generally disabled.

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          :            Gets the disk capacity of the server on which the database resides, sends an alert message when the threshold is reached, alerts the DBA to a storage planning plan
/************************************************************************************************************** ****
    Modified Date        Modified User        Version                    Modified Reason
    2013-05-6             Kerry                 V01.00.00                Modify the HTML output style. and disk capacity output to GB
*************************************************************************************************************** ****/
--============================================================================================================= =====
CREATE PROCEDURE [dbo]. [Sp_diskcapacity_alert2]
(
        @Threshold    NUMERIC
)
    
BEGIN
    
SET NOCOUNT on;
DECLARE @HtmlContent    
DECLARE @FreeRat        NUMERIC;
DECLARE @EmailHead        VARCHAR (200);
--Create temporary table to save server disk capacity information
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 (), DISKCD VARCHAR (12));
INSERT into #DriveInfo1 (DISKCD)
EXEC xp_cmdshell ' wmic logicaldisk get name ';
CREATE TABLE #DriveInfo2 (ID INT IDENTITY (), 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 isn't 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
        
        
            SET @EmailHead = ' Database disk capacity alarm (Alarm level 3) '
        ELSE IF @FreeRat >=5 and @FreeRat <=10
            SET @EmailHead = ' Database disk capacity alarm (Alarm level 4) '
        ELSE
            SET @EmailHead = ' Database disk capacity alarm (Alarm level 5) '
        
        SET @HtmlContent =
            +   N ' 
            +   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 ' 

            +   N ' <table  > '    
            +   N ' <tr><th> disk drive letter </th><th> total size (GB) </th><th> used space (GB) </th><th > Remaining space (GB) </th> '     
            +   N ' <th> used ratio (%) </th><th> remaining ratio (%) </th></tr > ' +    
            
            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)) +     
            
         
            
         EXEC Msdb.dbo.sp_send_dbmail     
            @profile_name = ' database_ddl_event ',--Specify your own profile_name       
            @recipients = ' [email protected]***.com ',         --Specify the mailbox you want to send to
            @subject = ' Server disk space alert ',     
            @body = @HtmlContent,   
            
        END
GO

Stored Procedure 3:SP_DISKCAPACITYALERT2.PRC

Note: This stored procedure does not use the above two stored procedures with security implications, but the overall information of the disk can not be obtained by a threshold to alarm, can only set the amount of space left on the disk when the alarm message generated.

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          :            Gets the disk capacity of the server on which the database resides, sends an alert message when the remaining capacity of a disk is below a certain value.
--                                          remind DBAs to plan for storage planning
/************************************************************************************************************** ****
    Modified Date        Modified User        Version                    Modified Reason
    2013-05-6                Kerry          V01.00.00           Modify the HTML output style. and disk capacity output to GB
*************************************************************************************************************** ****/
--============================================================================================================= =====
CREATE PROCEDURE [dbo]. [SP_DISKCAPACITY_ALERT3]
(
        @DiskCapacity    FLOAT
)
    
BEGIN
DECLARE @FreeSize         INT;
DECLARE @EmailHead        VARCHAR (200);
DECLARE @HtmlContent      
--Create temporary table to save server disk capacity information
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;
  
    BEGIN
        
        IF @FreeSize > 1
            
            SET @EmailHead = ' Database disk capacity alarm (Alarm level 3) '
        
            IF @FreeSize >= 0.5
                
                SET @EmailHead = ' Database disk capacity alarm (Alarm level 4) '
            
                SET @EmailHead = ' Database disk capacity alarm (Alarm level 5) '
        
        SET @HtmlContent = +n ' 
            + 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 ' 

            + @EmailHead + ' </H1> ' + N ' <table  > '
            + n ' <tr><th> disk letter </th><th> remaining space (GB) </th> ' + n ' </tr > '
            + CAST ((SELECT td = DISKCD,
                            ‘‘ ,
                            td = STR (Freesize * 1.0/1024, 6, 2),
                            ‘‘
                     From   #DiskCapacity
                   For
                     XML PATH (' tr '),
                         TYPE
                   
                   
           EXEC Msdb.dbo.sp_send_dbmail     
            @profile_name = ' database_ddl_event ',  --Specify your own profile_name   
            @recipients = ' [email protected]***.com ',          --Specify the mailbox you want to send to
              @subject = ' Server disk space alert ',     
            @body = @HtmlContent,   
            
            
    END
END
GO

MS SQL Monitors disk space alarms

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.