Disk Space Monitoring: How To

Keeping tabs on available disk space on your SQL Servers is something that every DBA should do—because once SQL Server runs out of disk on the underlying host, then everything obviously comes to a crashing halt. And while pro-actively sizing data and log file sizes is the best technique to use when it comes to database sizing, there are still plenty of situations where some databases (for whatever reason) might need to be allowed to auto-grow in many cases. As such, the last thing you want is for databases to grow to such a point that they run out of disk.

Related: Track Database Disk-Space Usage on a Granular Level

Ironically though, while it’s trivial to spot how much disk space is available on a server when you’re connected to it, it’s not exactly trivial to configure a way to get it to alert you when it starts running low on space. Happily though, there are some trivial ways to pull this off with SQL Server—provided you’ve configured your SQL Server to be able to send emails when a problem arises.

Checking Available Drive Space from within SQL Server

Prior to SQL Server 2008 R2 SP1, the best way to check on available disk space from within SQL Server is to use the undocumented xp_fixeddrives extended procedure. Using it is trivial—but for the fact that you can’t really do much with the results directly. Instead, if you want to do any type of programatic evaluation of the results from this extended proc, you have to output its results into a temporary table or table variable—and then you’re able to filter the output as desired. As such, in the following example, I’ve created a query that would give me a list of any or all drives with less than 5GBs of free disk available:

CREATE TABLE #drives (drive char,[free] int)INSERT INTO #drivesEXEC master..xp_fixeddrivesSELECT drive, [free] FROM #drives WHERE [free] < 5 * 1024

As of SQL Server 2008 R2 SP1 and above, Microsoft has actually made it easier to query the underlying server for details on available disk space through the use of a SUPPORTED dynamic management function: sys.dm_os_volume_stats(). And, while this DMF won’t give you access to all volumes on the server (only the ones where your databases 'live'), it is a bit easier to use in the sense that it can be directly filtered or queried without the need of creating temporary tables or table variables—as shown below:

WITH core AS ( SELECT DISTINCTs.volume_mount_point [Drive],CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs]FROM sys.master_files fCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s)SELECT [Drive],AvailableMBsFROM coreWHERE AvailableMBs < 5 * 1024

Do note, however, that it reports available space in bytes though—as opposed to MBs (which is easier, in my mind, to work with).

Sending Alerts

Of course, once you’re able to query this information directly, then all you need to do is set up a stored procedure or script that can be regularly executed as via SQL Server Agent job and which can send out alerts if a threshold is hit.

So, along those lines, what follows below are some sample stored procedures you can use in your own environment. The first one, listed below, is something that you could actually use on SQL Server 2000 and above systems—as it’s using a CDO to send email (via a call to a ‘custom’ stored procedure called sp_send_cdosysmail which is defined in an Microsoft KB article as a means of sending email from servers where SQL Mail (Outlook) wasn’t installed):

USE masterGOCREATE PROC dbo.sp_drivespace_alerts@from varchar(100),@to varchar(200),@subject varchar(100),@threshold int  -- number of MB under which to launch an alertASSET NOCOUNT ONDECLARE @msg varchar(500)SET @msg = 'Low Disk Space Notification. The following drives are currently reporting less than ' + CAST(@threshold as varchar(12)) + ' MB free: 'CREATE TABLE #drives (drive char,[free] int)INSERT INTO #drivesEXEC master..xp_fixeddrivesIF EXISTS (SELECT null FROM #drives WHERE [free] < @threshold) BEGINDECLARE @list varchar(30)SET @list = ''SELECT @list = @list + ' ' + drive + ',' FROM #drives WHERE [free] < @thresholdSET @list = LEFT(@list, LEN(@list) -1)SET @msg = @msg + @listPRINT @msg-- send the email... EXEC master..sp_send_cdosysmail @from, @to, @subject, @msgENDDROP TABLE #drivesRETURN 0GO

Where calls to this sproc (or what you’d define as the the Command for your Job Step) might look something similar to the following:

EXEC master.dbo.sp_drivespace_alerts@threshold = 12288, -- 12GB@from = '[email protected]',@subject = 'Low Disk Space Alerts - SERVERNAMEHERE',@to = '[email protected],[email protected]'

Or, if you’re using SQL Server 2005 and above, you could easily tweak the above sample to send mail via calls to sp_send_dbmail—which is baked in to SQL Server and which is pretty easy to use.

Likewise, if you’re using SQL Server 2008 R2 with SP1 and above (or SQL 2012, etc) then you could use the new sys.dm_os_volume_stats() DMF instead, and as such a combination of the new DMF and usage of sp_send_dbmail would end up looking more like this:

CREATE PROC dbo.sp_drivespace_alerts@from varchar(100),@to varchar(200),@subject varchar(100),@threshold int  -- number of MB under which to launch an alertASSET NOCOUNT ONDECLARE @list nvarchar(2000) = '';WITH core AS ( SELECT DISTINCTs.volume_mount_point [Drive],CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]FROM sys.master_files fCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s)SELECT @list = @list + ' ' + Drive + ', 'FROM coreWHERE AvailableMBs < @thresholdIF LEN(@list) > 3 BEGINDECLARE @msg varchar(500) = 'Low Disk Space Notification. The following drives are currently reporting less than ' + CAST(@threshold as varchar(12)) + ' MB free: ' + @listEXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxxxx',@recipients = @to,@subject = @subject,@body = @msgENDRETURN 0GO

Either way though, setting up a simple alerting system like this (i.e. via a schedule SQL Server agent job that runs, say, every 5 minutes) is a great way to help ensure that you don’t run into any nasty surprises.