Binaryworld - Monitoring Disk Space and Sending Alerts with TSQL ... [ SQL Server -> Server Operations ]

Monitoring disk space is one important task for SQL Server DBAs. To proactively monitor disk space, we want to be notified when disk space is below certain level. We also want to collect database file size information over time for trend analysis, for which Gregory Larsen has an excellent article "Avoiding the red zone". In addition, I also found it is helpful to collect free disk space data over time, since many SQL Servers are used for other purposes as well. Therefore, analyzing available disk space over time can give us a better idea of disk usage.

Normally, using WSH (Windows Scripting Host) and WMI (Windows Management Instrumentation) is a better way of gathering disk information. However, it is still helpful to do this in T-SQL, especially for DBAs who don't have access to the Windows server itself. This sounds strange, but is actually pretty common. Many DBAs's only way of managing SQL Server is through Enterprise Manager and Query Analyzer. Of course, one could use master..xp_cmdshell or a CmdExec job to bypass this limitation, but it is still handy to know the T-SQL way of doing things.

In this article, I will address 2 disk related issues:

how to use a stored procedure to send an email alert when disk free space is below a given level;
how to use a stored procedure to collect disk available space data and store that information in a table.
Both stored procedures use one SQL Server extended stored procedure, master..xp_fixeddrives

Stored procedure to send an email alert when disk free space is below a certain level
The following is a stored procedure I wrote to alert DBAs when disk space is below a given limit. Depending on your file growth rate, you can schedule a job to run this stored procedure weekly, daily, or hourly. In my case, running this job daily served my purpose.

Note that I separated the C Drive from the other disk drives, as the OS is usually installed on C. In my code, if C drive has less than 1 GB(1024 MB), then an alert email will be sent. For the other drives, the default benchmark value is 2 GB. You can change these values to suit your specific needs.

Note that temp tables and cursors are used in this stored procedure. Temp table, as opposed to table variable, is necessary because you cannot insert results from an extended stored procedure into a table variable. Also, the cursor is defined as FAST_FORWARD, because it is read-only and direction is not important to us. The rest of the code should be self-explanatory. If you have SQL Mail configured properly, just replace the @recipients value and this procedure should work.

Click here to copy the following block

CREATE PROCEDURE usp_DiskFreeSpaceAlert
@DriveCBenchmark int = 1024,
@OtherDataDriveBenchmark int = 2048
AS
--By: Haidong "Alex" Ji This procedure sends out an alert message when hard disk space is below a predefined value. This procedure can be scheduled to run daily so that DBA can act quickly to address this issue.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)

DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)

/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC master..xp_fixeddrives

SELECT @DiskFreeSpace = FreeMB FROM #disk_free_space where DriveLetter = 'C'

IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' + CAST(@DiskFreeSpace AS VARCHAR) + ' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End

DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #disk_free_space where DriveLetter not in ('C')

open DriveSpace
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace

WHILE (@@FETCH_STATUS = 0)
Begin
if @DiskFreeSpace < @OtherDataDriveBenchmark
Begin
set @MailSubject = 'Drive ' + @DriveLetter + ' free space is low on ' + @@SERVERNAME
set @AlertMessage = @DriveLetter + ' has only ' + cast(@DiskFreeSpace as varchar) + ' MB left. Please increase free space for this drive immediately to avoid production issues'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace
deallocate DriveSpace
DROP TABLE #disk_free_space
GO

Stored procedure to collect disk available space data and store that information in a table
As mentioned earlier, for capacity planning and trend analysis purpose, sometime it is not enough just to collect database file growth data overtime. It is beneficial to also have disk usage data overtime. To achieve that, I wrote the following stored procedure to collect disk available space data and store that into a table. Again, you can schedule a job that runs weekly or daily, depending upon your specific needs, to collect this data over time for trend analysis.

For DBA administrative purposes, I always create a database called DBA to store database admin-related data. In this case, I create a table to store available disk space information. This table has 4 columns: identity column, Drive Letter column, Available MB column, and a time stamp column with a default value of GetDate(). See the following DDL (Data Definition Language) for this table.

Click here to copy the following block

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DiskAvailableSpace]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DiskAvailableSpace]
GO

CREATE TABLE [dbo].[DiskAvailableSpace] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[DriveLetter] [char] (1),
[FreeMB] [int] NOT NULL ,
[TimeCollected] [smalldatetime] NOT NULL
)
GO

ALTER TABLE [dbo].[DiskAvailableSpace] WITH NOCHECK ADD
CONSTRAINT [DF_DiskAvailableSpace_TimeCollected] DEFAULT (getdate()) FOR [TimeCollected]
GO

The following is the code for this stored procedure. After this stored procedure is executed, the results will be saved in the DiskAvailableSpace table. If you schedule this procedure to run every week, after a few weeks, you will be able to draw a chart of disk usage. This can be pretty valuable for trend analysis.

Click here to copy the following block

CREATE PROCEDURE usp_TrackDiskAvailableSpace AS
/*
Author: Haidong Ji Date: 1/21/2003
Purpose: Trace and record xp_fixeddrives results into the DiskAvailableSpace table. The results will be invaluable
for trend analysis.
*/
SET NOCOUNT ON
/* Create a temp table to hold disk space information */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL
,FreeMB INTEGER NOT NULL
)

/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC master..xp_fixeddrives

/* Populate DiskAvailableSpace with free space data. This table will have an identity field and time stamp */
INSERT INTO DiskAvailableSpace
(DriveLetter, FreeMB)
SELECT DriveLetter, FreeMB FROM #disk_free_space
/* Clean up. Drop the temp table */
DROP TABLE #disk_free_space
GO

Conclusion
The above 2 stored procedures enabled me to proactively monitor disk usage information. I scheduled the disk space alert stored procedure daily. I also scheduled the TrackDiskSpaceAvailable stored procedure weekly. Those 2 stored procedure really server me well so far. You can increase the job frequency as needed in your environment. Hopefully they can help you as well.