Monday, October 22, 2012

SQL Server- How to send Insufficient Disk Space Notification


1.     Background

The basic aim of this article is to describe a way of sending email notification whenever insufficient disk space found in any of the drive of the server/computer. This notification is useful while supporting and maintenance of servers. Here we are using xp_fixeddrives  system stored procedure for getting the disk space information from the server/computer and Database Mail for sending notification. For configuring the Database mail, you can refer the below link:

2.     Pre-requisite                                    


1.      Database Mail configuration should be configured for sending email notification.
2.      SQL Server Agent service should run.

3.     What is xp_fixeddrives?

xp_fixeddrives is an extended stored procedure that provides free space availability details of all the disk drives available in server/computer. This stored procedure returns amount of free space available in MB.

4.     Step by Step procedure to send Insufficient Disk Space Notification:

                    i. We have created a stored procedure named “GetServerSpaceStatus” that will return the disk drive name and its available free space in MB if any of the disk space is equal or less than 100 MB.
We have set the criteria for insufficient disk space is equal or less than 100 MB. Below is the stored procedure that uses xp_fixeddrives extended stored procedure with filter condition of 100 MB or less free space of disk drive.

        /*******************************************************
      CHANGE HISTORY
 ********************************************************
    Date:                Author:            Description: (CR#, Ver, Bug#   etc)
   -----------          -----------          -------------------
   21-Oct-20     Vishal Jharwade      1. The purpose of the SP is to find the drive 
                                         name which is having insufficient disk space.
                                     2. Criteria for insufficient disk space- 100 MB
  ********************************************************/

 CREATE PROCEDURE [dbo].[GetServerSpaceStatus]
 AS
 BEGIN
       SET NOCOUNT ON

       DECLARE              @sErrorMessage       AS NVARCHAR(255)
       DECLARE              @lErrorMessageID     AS INT
       DECLARE              @lReturnCode         AS INT
       DECLARE              @sMessage            AS NVARCHAR(4000)   
       DECLARE              @lIdentity           AS INT
       DECLARE              @DiskDrive           AS NVARCHAR(100)
       DECLARE              @DiskSpace           AS INT
       DECLARE              @SUBJECTMESSAGE NVARCHAR(500)
       DECLARE              @tableHTML NVARCHAR(500)

BEGIN TRY

       --Declaring table variable for storing Disk space information
       DECLARE @DiskFreeSpace AS TABLE
       (
        Drive CHAR(1),
        MB_Free INT
       )
       --Inserting disk space availability details into table variable
       INSERT INTO @DiskFreeSpace
       EXEC xp_fixeddrives

       --Storing drive name and free space(in MB) in variable.
       --Creteria for insufficient disk space equal or less than 100 MB
       SELECT  @DiskDrive= Drive ,
               @DiskSpace = MB_Free
       FROM @DiskFreeSpace
       WHERE MB_Free < 100

SET @SUBJECTMESSAGE= 'Production Support Mail: Insufficent Disk Space in ' +
@DiskDrive + ' Drive in SQLCircuit Blogspot Server'
SET @tableHTML = 'This is to notify you that Insufficient disk space encountered
                  in' + @DiskDrive + ' Drive' + '('+ + ')' + 'in SQLCircuit
Blogspot Server, Please take necessary action to avoid any further issues' + 
CHAR(4) + 'Thanks,' + CHAR(4) +'SQLCircuit Team'

      --Sending Mail       
      EXEC msdb.dbo.sp_send_dbmail
                     @recipients='sqlcircuit@gmail.com',   
                     @subject = @SUBJECTMESSAGE,   
                     @Profile_Name='sqlcircuit',  --Profile of SMTP Server 
                     @body =  @tableHTML,   
                     @body_format = 'HTML' ;

      SET @lReturnCode = 0

END TRY 
       BEGIN CATCH  
             --logging Error information
             INSERT INTO [dbo].[ErrorLog]
                      ([MessageID],[ErrorNumber],[ErrorSeverity],[ErrorState],
                      [ErrorProcedure],[ErrorLine],[ErrorMessage],[CreatedBy],
                      [CreatedDate])                    
             VALUES
            (1,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ISNULL(ERROR_PROCEDURE(), '-
            '),ERROR_LINE(),ERROR_MESSAGE(),'',GETDATE())  

END CATCH 
       RETURN @lReturnCode
       SET NOCOUNT OFF           
END

ii. Execute the stored procedure and check the email whether we have received mail or not.
SP Execution:


 Check the email to confirm whether we have received email or not: Below screenshot is showing that we have received Insufficient Disk Space notification:


5.     How to use this approach in practical scenarios:

We can create a SQL Agent Job that should run continuously or with minimum time interval (1 or 2 mins). In this job, we can call the stored procedure GetServerSpaceStatus that will return Disk drive name with insufficient disk space.

6.      Conclusion

By using the above steps, we can implement Insufficient Disk Space Notification. 

----------------------------------------------------End of Document---------------------------------------------------

1 comment: