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 #drives EXEC master..xp_fixeddrives SELECT 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 DISTINCT s.volume_mount_point [Drive], CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs] FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s ) SELECT [Drive],AvailableMBs FROM core WHERE 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 master GO 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 alert AS SET NOCOUNT ON DECLARE @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 #drives EXEC master..xp_fixeddrives IF EXISTS (SELECT null FROM #drives WHERE [free] < @threshold) BEGIN DECLARE @list varchar(30) SET @list = '' SELECT @list = @list + ' ' + drive + ',' FROM #drives WHERE [free] < @threshold SET @list = LEFT(@list, LEN(@list) -1) SET @msg = @msg + @list PRINT @msg -- send the email... EXEC master..sp_send_cdosysmail @from, @to, @subject, @msg END DROP TABLE #drives RETURN 0 GO
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 alert AS SET NOCOUNT ON DECLARE @list nvarchar(2000) = ''; WITH core AS ( SELECT DISTINCT s.volume_mount_point [Drive], CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs] FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s ) SELECT @list = @list + ' ' + Drive + ', ' FROM core WHERE AvailableMBs < @threshold IF LEN(@list) > 3 BEGIN DECLARE @msg varchar(500) = 'Low Disk Space Notification. The following drives are currently reporting less than ' + CAST(@threshold as varchar(12)) + ' MB free: ' + @list EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxxxx', @recipients = @to, @subject = @subject, @body = @msg END RETURN 0 GO
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.