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:

http://sqlcircuit.blogspot.in/#!/2012/10/sql-server-implementation-of-database.html

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---------------------------------------------------

Implementation of Database Mail

1.     Background

The purpose of this article is to describe implementation of database mail configuration in SQL Server so that Database Mail can be utilized for sending email in various applications. Database Mail is easy to configure and maintain as well as one of the simpler way of implementing email notifications in the application.

2.     Pre-requisite                                    

1.   Database Mail configuration should be setup by service account which will be used for sending email notification.

2.   SQL Server Agent service should run.

3.   Service Account should have right of sending mail.

3.     Step by Step procedure to implement database mail:

                                i. Go to SQL Server Management Studioè Connect to the required SQL serve instanceè go to Management folderèDatabase MailèRight click on the Database Mailè click on Configure Database Mail:

  

                              ii. It will open the database mail configuration wizard, Click next

                            iii. Select the highlighted Database mail option and click next

                             iv. Give the name of the Profile e.g SQLCircuit and Description. Click on Add

                               v. Specify the details for the account which will be used for sending the mail e.g India\SQLCircuit :

We are using the following details for configuring Database Mail:

Account Name	India\SQLCircuit
SMTP Server	Smtp.gmail.com
SMTP port	465
Authentication	·         We are using Basic authentication here. For basic authentication, we need to pass store username and password in the database profile itself to authenticate on the SMTP server. ·         Windows Authentication is useful when we use SQL Server service account for authentication in SMTP server. ·         Anonymous Authentication: The SMTP server does not require any authentication. Database Mail will not use any credentials to authenticate on the SMTP server

                             vi. Now click on next to map Account to the profile. Here we have added Account name “India\SQLCircuit” to profile “SQLCircuit”

 

                           vii. Click next. It will open “Manage Profile Security”. Here you can make the profile public or private based on the requirement. Also you can set the profile as default.

                         viii. Click next, it will show system parameters for Database Mail

                             ix. Click Next to complete the wizard

Click finish.

4.     How to send email using Database Mail

                                i. For unit testing, go to Database mail, right click and select Send Test E-Mail

                              ii. Select the SQLCircuit Profile and give the email address to which you want to send notification. For testing purpose, I am sending mail to sqlcircuit@gmail.com  Click on ‘Send Test E-Mail’.

                            iii. Check the email and confirm that whether email is received or not. Below screenshot is showing that we have received the mail.

                             iv. We can send mail by using sp_send_dbmail system procedure. sp_send_dbmail is used send mail by using database mail profile configured in SQL Server. Use the below code to send mail to sqlcircuit@gmail.com

DECLARE @SUBJECTMESSAGE NVARCHAR(500)

DECLARE @tableHTML NVARCHAR(500)

SET @SUBJECTMESSAGE= 'Database Mail configuration has been completed'

SET @tableHTML = 'This is to inform you that Database Mail configuration has been setup in SQLCircuit Server.

Please utilize the notification services and please contact us at sqlcircuit@gmail.com for any queries

Thanks,

SQLCircuit Team

'

EXEC msdb.dbo.sp_send_dbmail

                     @recipients='sqlcircuit@gmail.com',   

                     @subject = @SUBJECTMESSAGE,   

                     @Profile_Name='sqlcircuit',  --Profile of SMTP Server 

                     @body =  @tableHTML,   

                     @body_format = 'HTML' ;

                               v. Check the email and confirm that whether email is received or not. Below screenshot is showing that we have received the mail.

5.     How to see the Database Mail Log

For checking the log of Database mail, right click on Database Mail and Select “View Database Mail Log”

It will show the complete log of Database Mail. We can utilize the log for troubleshooting the issues with database mail.

6.     Conclusion

By using the above steps, we can configure the Database Mail and can be utilize for sending email notification in various application.

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