Disk Space Monitoring: How To

SQL Server data storage rackKeeping 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 = 'sqlalerts@noreply.com',
        @subject = 'Low Disk Space Alerts - SERVERNAMEHERE',
        @to = 'youremail@address.here,other@email.here'

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.

Discuss this Blog Entry 1

on Mar 25, 2013
Hi, using the SQL Server 2008 R2 version, I am getting: Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@from". Msg 137, Level 15, State 2, Line 22 Must declare the scalar variable "@threshold". Msg 137, Level 15, State 2, Line 26 Must declare the scalar variable "@threshold". Msg 137, Level 15, State 2, Line 29 Must declare the scalar variable "@to". Msg 178, Level 15, State 1, Line 35 A RETURN statement with a return value cannot be used in this context. I have defined a mail profile and have added it to the script. I tried to add the threshold value. What do I do wrong? Thanks Stefan

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×