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

3 Replies to “Setup SQL Email Alert for Disk Space Usage”

  1. Perfect. I was looking exactly for something like that. Simple, elegant, without the need to create persisted tables, stored procedures, etc… (Which there are plenty when you Google for it. (People LOVE to complicate things, I don’t know why…))
    I just needed a quick script that I could use as a step in a SQL Server Agent Job that checks every hour. Honestly I could have written it myself, but you saved me at least half an hour of figuring out the syntax and code. Thank you much. Best, Raphael.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.