Monitor Disk Space on your SQL Server

I’m a SQL Server developer; I get to wear many hats though. My previous job was as a SQL Server guy, my current job is more of that + Support + Sales + Web Guy. I’m currently working with a server that really ought to be upgraded, but for the time being this is not going to happen. I’m dealing with low disk space and memory issues more often than I would like.

I was actually researching something completely different but stumbled upon someone who was using xp_sendmail to email himself alerts whenever his server was low. I thought this was a good idea so I was inspired to write my own.

Essentially I’m just creating a temporary table and using a stored procedure I had never heard of before called xp_fixeddrives. If you just pop open your Query Analyzer and exec xp_fixeddrives you’ll see it just returns a simple result showing hard drives and disk space. I’m then inserting into this temporary table the results from the stored procedure, and if it’s below some figure I’ve defined (in this case 10GB) then prepare an email. xp_sendmail comes with some specific arguments, I’ve just used @recipients, @subject and @message. I’ll include a link at the bottom so you can read more about xp_sendmail and what other arguments you can use.

So, the code:

DECLARE @MBFree INT

--// Create the temporary table to store values
CREATE TABLE #Space(Drive CHAR(1), MBFree INT)

--// Call the xp_fixeddrives stored procedure and insert it
INSERT INTO #Space EXEC xp_fixeddrives

--// Select statement to then analyze
SELECT @MBFree = MBFree FROM #Space WHERE Drive = 'C' 

--// If Free Space is less than threshold
IF @MBFree < 10240

--// Declare the body of the email
DECLARE @body VARCHAR(8000)
SET @body = 'Free space on C: has dropped below 10GB. Current Space is '
SET @body = @body + CONVERT(VARCHAR, @MBFREE/1000)
SET @body = @body + ' GB'

--// Declare the subject of the email
DECLARE @mailsubject VARCHAR(500)
SET @mailsubject = 'C Drive Low - Currently '
SET @mailsubject = @mailsubject + CONVERT(VARCHAR, @MBFREE/1000)
SET @mailsubject = @mailsubject + ' GB' 

--// Send the email using xp_sendmail
EXEC master.dbo.xp_sendmail
@recipients = 'michael@mydomain.com',
@subject = @mailsubject, 
@message = @body

--// Drop the temporary table
DROP TABLE #Space

It could probably be done prettier and with better defined fields, but it is what it is.

You can read more about xp_sendmail here:
http://msdn2.microsoft.com/en-us/library/ms189505.aspx