MS SQL Monitors disk space alarms

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