Setup SQL Email Alert for Disk Space Usage

I had an application go kaput on me all of a sudden and that wasn’t good. I had gotten back from lunch (always happens when I get back from lunch) and was immediately approached by the Sys Admin saying that a certain web application couldn’t connect to the SQL database. He wanted me to check out why and get back to him ASAP.

So I quickly log into the database server and see that SQL server is running fine, the SQL account that the web application uses is not “locked” and the password expiration is not checked (meaning that technically the password for that SQL account should never expire). I look at the disk drives by clicking on “My Computer” in Windows Explorer and quickly saw …

“E: Drive 0 GB free of 99 GB”

We have SQL Server configured to store Audit files on the “E” drive. I went into the folder and removed the oldest month worth of audit files. That freed up about 20 GB of space.

Roughly 5 minutes later the same Sys Admin ran in my office saying, “hey! I reset the SQL Account password and it works now.”

Hmm…

I know. What a coincidence right? :)

I believe the reason why the application couldn’t connect to the database is because SQL Server couldn’t write any more audit files to the disk drive. As a result, SQL Server refused outside connections.

I wrote a script that checks whether a specific disk drive space goes below a given threshold. If so, it will send the DBAs an email alert.

Here is the script. Feel free to use/modify it however you see fit.

--create temp table for results
create table #freespace
(drive char(1),mb_free int)

--insert drive data into temp table
insert into #freespace exec sys.xp_fixeddrives

declare @subject varchar(100)
declare @profile_name varchar(25)
declare @body varchar(200)
declare @gb_free int
declare @recipients varchar(50)

--you can specify whatever drive leter
select @gb_free = (mb_free / 1024) from #freespace where drive = 'E'

--you can specify whatever number you want. I put 20GB.
if (@gb_free < 20)
 BEGIN
  SET @profile_name = 'Profile Name goes here'
  SET @recipients = 'your@email.com'
  SET @subject = 'ALERT!! E: drive is BELOW 20 GB'
  SET @body = 'Please check the E: drive! It has fallen below 20 GB of free space. There is currently ' + CONVERT(varchar,@gb_free) + ' GB of free space left on the E drive.'
  
  exec msdb.dbo.sp_send_dbmail
  @profile_name = @profile_name,
  @body = @body,
  @subject = @subject,
  @recipients = @recipients
 END
--drop temp table
drop table #freespace;
go

(UPDATE: 2024) Per commenter’s question; if you have multiple drives and want a single email that will list all drives that have more than 20GB limit, then this snippet should do the job.

-- Create temp table for results
create table #freespace (drive char(1), mb_free int)

-- Insert drive data into temp table
insert into #freespace exec sys.xp_fixeddrives

-- Declare variables
declare @drive char(1)
declare @gb_free int
declare @threshold int
declare @profile_name varchar(25)
declare @recipients varchar(50)
declare @subject varchar(100)
declare @body varchar(max)
declare @alert_body varchar(max)

-- Set the threshold value (in GB)
set @threshold = 20
-- Set the email profile name
set @profile_name = 'Profile Name goes here'
-- Set the recipients
set @recipients = 'your@email.com'
-- Initialize the alert body variable
set @alert_body = ''

-- Cursor to loop through all drives in the temp table
declare drive_cursor cursor for
select drive, (mb_free / 1024) as gb_free from #freespace

-- Open the cursor
open drive_cursor

-- Fetch the first row from the cursor
fetch next from drive_cursor into @drive, @gb_free

-- Loop through all rows
while @@fetch_status = 0
begin
    -- Check if the free space is below the threshold
    if (@gb_free < @threshold) begin -- Append the drive information to the alert body set @alert_body = @alert_body + 'Drive ' + @drive + ': has fallen below ' + cast(@threshold as varchar) + ' GB of free space. There is currently ' + convert(varchar, @gb_free) + ' GB of free space left.' + char(13) + char(10) end -- Fetch the next row from the cursor fetch next from drive_cursor into @drive, @gb_free end -- Close and deallocate the cursor close drive_cursor deallocate drive_cursor -- If there are any alerts, send an email if len(@alert_body) > 0
begin
    -- Set the email subject and body
    set @subject = 'ALERT!! Drives below ' + cast(@threshold as varchar) + ' GB of free space'
    set @body = 'Please check the following drives:' + char(13) + char(10) + @alert_body
    
    -- Send the email
    exec msdb.dbo.sp_send_dbmail 
        @profile_name = @profile_name, 
        @body = @body, 
        @subject = @subject, 
        @recipients = @recipients
end

-- Drop temp table
drop table #freespace
go