How to send an email alert from MS SQL Server Instance when a given disk space is lower than a specified threshold

Several days ago, we received a request from one of our clients asking if we can set an automated email notification when their disk space is lower than a specified threshold. We learned that they run into an issue when one of their disks on the SQL Server were almost full and their nightly process could not properly complete which led to a series of issues on the following day. Even though they were using the “Auto Growth” feature in their database configurations they could not prevent this issue to happen.

To accomplish their request, we decided to setup a SQL Server Agent Job that executes a stored procedure. The stored procedure will check the any disk space based on the threshold values in the threshold tables, then, if the disk space is lower than the threshold values, the script will send a formatted email to the addresses specified in the email table.

*Note that the stored procedure will check each and every disk spaces on the server, it will not be limited to the drives that holds the SQL Server data files.

But before we start please keep in mind:

It is not ideal to have the process doing the monitoring to run on the same machine it is monitoring (If the monitored machine experiences an issue then the process may not be able to notify anyone. So, if it is possible, best practice to run this process on a different environment.)
And also, exchange server needs to be set up to be able to send emails.

Here is a sample of an email alert when disk space is lower than a specified threshold:

And here is the dynamic solution that provides you all the information you need:

Tables:

Table1

USE [Reporting];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[Disk_Space_Alert_Threshold] (
[Disk_Space_Alert_Threshold_Id] int IDENTITY(1, 1) NOT NULL,
[Alert_Class] varchar(25) NULL,
[Alert_Threshold_Value] int NULL)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
GO
ALTER TABLE [dbo].[Disk_Space_Alert_Threshold] SET (LOCK_ESCALATION = TABLE);
GO

-- This table holds a red and a yellow values.
-- So disk space availability for the yellow alert is set to 15%
-- And the red alert is set to 10%
SET IDENTITY_INSERT [dbo].[Disk_Space_Alert_Threshold] ON
INSERT [dbo].[Disk_Space_Alert_Threshold](
        [Disk_Space_Alert_Threshold_Id],
        [Alert_Class],
        [Alert_Threshold_Value])
VALUES
  (
    1,
    N'Red',
    10)
INSERT[dbo].[Disk_Space_Alert_Threshold](
        [Disk_Space_Alert_Threshold_Id],
        [Alert_Class],
        [Alert_Threshold_Value])
VALUES
  (
    2,
    N'Yellow',
    15)
SET IDENTITY_INSERT [dbo].[Disk_Space_Alert_Threshold] OFF

Table2

USE [Reporting];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[Email_List] (
[Email_List_Id] int IDENTITY(1, 1) NOT NULL,
[First_Name] varchar(255) NULL,
[Last_Name] varchar(255) NULL,
[Email_Address] varchar(255) NULL,
[Active] bit NULL)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
GO
ALTER TABLE [dbo].[Email_List] SET (LOCK_ESCALATION = TABLE);
GO

-- This table holds the email addresses of the people you would like to be notified when the disk space is running low.
SET IDENTITY_INSERT [dbo].[StaffEmailList] ON
INSERT [dbo].[Email_List] (
      [Email_List_Id]
      ,[First_Name]
      ,[Last_Name]
      ,[Email_Address]
      )
VALUES (
      1
      ,N'Tony'
      ,N'Stark'
      ,N'T.S@starktech.com'
      )
SET IDENTITY_INSERT [dbo].[Email_List] OFF

Store Procedure:

USE [Reporting];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[Low_Disk_Space_Alert]
WITH EXEC AS CALLER
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DiskSpaceStats') IS NOT NULL
      DROP TABLE #DiskSpaceStats
IF OBJECT_ID('tempdb..#DriveAlert') IS NOT NULL
      DROP TABLE #DriveAlert
DECLARE @profileName AS VARCHAR(128)
DECLARE @thresholdRed AS INT
DECLARE @thresholdYellow AS INT
DECLARE @drive AS VARCHAR(5)
DECLARE @sql AS VARCHAR(MAX)
DECLARE @i AS INT
DECLARE @j AS INT
/*please note that you need to change the name for your SQL Server email profile at below*/
SET @profileName = 'DBMailProfile'
CREATE TABLE #DriveAlert (
      AlertEntry VARCHAR(25)
      ,AlertValue VARCHAR(25)
      )
SET @thresholdRed = (
            SELECT Alert_Threshold_Value
            FROM [Reporting].[dbo].[Disk_Space_Alert_Threshold]
            WHERE Alert_Class = 'Red'
            )
SET @thresholdYellow = (
            SELECT Alert_Threshold_Value
            FROM [Reporting].[dbo].[Disk_Space_Alert_Threshold]
            WHERE Alert_Class = 'Yellow'
            )   
declare @svrName varchar(255)
declare @sql1 varchar(400)
set @svrName = @@SERVERNAME
set @sql1 = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--temporary table to hold the disk name, total space and free space value in to temporary table
CREATE TABLE #output
(line varchar(255))
insert #output
EXEC xp_cmdshell @sql1
SELECT ROW_NUMBER() OVER (
            ORDER BY AvailableDriveSpacePercentage ASC
            ) AS RowNo
      ,ObservationDT
      ,Drive
      ,AvailableDriveSpacePercentage
      ,AvailableDriveSpaceGB
      ,UsedDriveSpaceGB
      ,TotalDriveSpaceGB
INTO #DiskSpaceStats
FROM (
--script to retrieve the values in GB
select
DISTINCT GETDATE() AS ObservationDT,
rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as Drive,
   cast((round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0))/ 
   (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0))
   *100 AS NUMERIC(8, 2))as AvailableDriveSpacePercentage,
    round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'AvailableDriveSpaceGB',
   (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0))-
   ( round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)) as   UsedDriveSpaceGB,
   round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'TotalDriveSpaceGB'
from #output
where line like '[A-Z][:]%'
      ) AS r
SET @i = 1
SET @j = (
            SELECT MAX(RowNo)
            FROM #DiskSpaceStats
            )
WHILE @i <= @j
BEGIN
      SET @drive = (
                  SELECT Drive
                  FROM #DiskSpaceStats
                  WHERE RowNo = @i
                  )
      SET @sql = 'DECLARE @alert AS VARCHAR(6)
IF ' + CONVERT(VARCHAR(3), @thresholdYellow) + ' > (
            SELECT AvailableDriveSpacePercentage
            FROM #DiskSpaceStats
            WHERE Drive = ''' + @drive + '''
            )
BEGIN
SET @alert = ''YELLOW''
END
IF ' + CONVERT(VARCHAR(3), @thresholdRed) + ' > (
            SELECT AvailableDriveSpacePercentage
            FROM #DiskSpaceStats
            WHERE Drive = ''' + @drive + '''
            )
BEGIN
SET @alert = ''RED''
END
IF @alert IS NOT NULL
BEGIN
      SELECT AlertEntry
      ,AlertValue
      FROM (
      SELECT ''Alert Class: '' AS AlertEntry
      ,@alert AS AlertValue
      UNION ALL
      SELECT ''DateTime: '' AS AlertEntry
      ,CONVERT(VARCHAR(30), ObservationDT)  AS AlertValue
      FROM #DiskSpaceStats
      WHERE Drive = ''' + @drive + '''
      UNION ALL
      SELECT ''Drive: '' AS AlertEntry
      ,Drive AS AlertValue
      FROM #DiskSpaceStats
      WHERE Drive = ''' + @drive +
            '''
      UNION ALL
      SELECT ''Percentage Available: '' AS AlertEntry
      ,CONVERT(VARCHAR(10), AvailableDriveSpacePercentage) + ''%'' AS AlertValue
      FROM #DiskSpaceStats
      WHERE Drive = ''' + @drive + '''
      UNION ALL
      SELECT ''Available Space: '' AS AlertEntry
      ,CONVERT(VARCHAR(10), AvailableDriveSpaceGB) + '' GB'' AS AlertValue
      FROM #DiskSpaceStats
      WHERE Drive = ''' + @drive + '''
      UNION ALL
      SELECT ''Used Space: '' AS AlertEntry
      ,CONVERT(VARCHAR(10), UsedDriveSpaceGB) + '' GB'' AS AlertValue
      FROM #DiskSpaceStats
      WHERE Drive = ''' + @drive + '''
      UNION ALL
      SELECT ''Total Space: '' AS AlertEntry
      ,CONVERT(VARCHAR(10), TotalDriveSpaceGB) + '' GB'' AS AlertValue
      FROM #DiskSpaceStats
      WHERE Drive = ''' + @drive + '''
      UNION ALL
      SELECT '' '' AS AlertEntry
      ,'' '' AS AlertValue
      ) AS alert
END
'
      INSERT INTO #DriveAlert
      EXEC (@sql)
      SET @i = @i + 1
END
IF EXISTS (
            SELECT *
            FROM #DriveAlert
            )
BEGIN
      DECLARE @emailList AS VARCHAR(MAX)
      DECLARE @subjectMsg AS VARCHAR(255)
      DECLARE @tableHTML NVARCHAR(MAX)
      SET @emailList = (
                  SELECT STUFF((
                                    SELECT '; ' + Email_Address
                                    FROM [Reporting].[dbo].[Email_List]
                                    WHERE Active = 1
                                    FOR XML PATH('')
                                    ), 1, 1, '') AS Email_Address
                  )
      SET @subjectMsg = (
                  SELECT @@SERVERNAME
                  ) + ' Low Disk Space Alert'
      SET @tableHTML = N'<style>
      .tableFormat {
            width:80%;
            border:1px solid #C0C0C0;
            border-collapse:collapse;
            padding:5px;
      }
      .tableFormat th {
            border:1px solid #C0C0C0;
            padding:5px;
            background:#F0F0F0;
      }
      .tableFormat td {
            border:1px solid #C0C0C0;
            text-align:right;
            padding:5px;
      }
</style>' + N'<H1></H1>' + N'<table class="tableFormat" align="center">' + N'<tr><th>Description</th><th>Value</th></tr>' + CAST((
                        SELECT td = AlertEntry
                              ,''
                              ,td = AlertValue
                              ,''
                        FROM #DriveAlert
                        FOR XML PATH('tr')
                              ,TYPE
                        ) AS NVARCHAR(MAX)) + N'</table>';
      DROP TABLE #DiskSpaceStats
      SET @tableHTML = REPLACE(@tableHTML, '<td> </td>', '<td bgcolor="#F0F0F0"> </td>')
      SET @tableHTML = REPLACE(@tableHTML, '<td>RED</td>', '<td bgcolor="red"><b>RED</b></td>')
      SET @tableHTML = REPLACE(@tableHTML, '<td>YELLOW</td>', '<td bgcolor="yellow"><b>YELLOW</b></td>')
      EXEC msdb.dbo.sp_send_dbmail @profile_name = @profileName
            ,@recipients = @emailList
            ,@body = @tableHTML
            ,@subject = @subjectMsg
            ,@importance = 'High'
            ,@body_format = 'HTML';   
DROP TABLE #output
END
GO

Job:

-- This job simply scheduled to run the stored procedure every 10 minutes. Change as you wish.
USE [master];
GO
DECLARE @JobID BINARY(16)
EXECUTE msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'Low_Disk_Space_Alert',
   @enabled = 1,
   @owner_login_name = N'sa',
   @description = N'No description available.',
   @category_name = N'[Uncategorized (Local)]',
   @notify_level_eventlog = 0,
   @notify_level_email = 0,
   @notify_level_netsend = 0,
   @notify_level_page = 0,
   @delete_level = 0;
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N' Low_Disk_Space_Alert_SP_Run',
   @command = N'exec [dbo].[Low_Disk_Space_Alert]',
   @database_name = N'Reporting',
   @subsystem = N'TSQL',
   @flags = 0,
   @retry_attempts = 0,
   @retry_interval = 0,
   @on_success_step_id = 0,
   @on_success_action = 1,
   @on_fail_step_id = 0,
   @on_fail_action = 2;
EXECUTE msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1;
EXECUTE msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @JobID,
        @name = N' Low_Disk_Space_Alert_Schedule',
        @enabled = 1,
        @freq_type = 4,
        @active_start_date = 20210319,
        @active_end_date = 99991231,
        @freq_interval = 1,
        @freq_subday_type = 4,
        @freq_subday_interval = 10,
        @freq_relative_interval = 1,
        @freq_recurrence_factor = 1,
        @active_start_time = 0,
        @active_end_time = 235959;
GO

IT Blog