Using xp_fixeddrives to Monitor Free Space

By Gregory A. Larsen

As a DBA one of your responsibilities is to monitor disk space. You always need to make sure you have enough for your databases, database backups and any other types of files you plan to store on your server. If you don't monitor your disk space and make sure you have enough space, eventually one of your critical database processes or components is going to fail because it can't allocate the disk space it needs.

There are a number of different methods to monitor disk space usage. I'm not going to discuss the different methods you could use, I'm only going to write about one. The method I am going to discuss is a simple space monitoring method that uses an undocumented SQL Server extended stored procedure named xp_fixeddrives. The xp_fixeddrives extended stored procedure returns a record set that contains the number of megabytes of free space for each physical drive associated with the SQL Server machine.

Now it is simple enough just to run the xp_fixeddrives command every so often from Query Analyzer to review the amount of free space, although this method is too time consuming for busy Database Administrators. A better method would be to automate running this command periodically to review the amount of free space. With the automated approach you can perform what ever you feel is appropriate with the free space information. I can think of a couple of different DBA tasks where free space information might be valuable. The first thing would be alerting the DBA when free space drops below a specific threshold on any SQL Server drive, and the second would be to historically track free space for disk space capacity management.

First, let's look at how to build a process to alert the DBA when any one of the SQL Server disk drives falls below a predetermined threshold. In order to use the xp_fixeddrives information in this automated alerting process I need to get the information outputted by the extended store procedures into a SQL Server table or application variables, so I can programmatically make decisions on whether a drive has passed the free space threshold. To get the xp_fixeddrives information into a temporary table I use the following T-SQL.

create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives

As you can see, I created a temporary table named #FreeSpace to insert the records that xp_fixeddrives outputs. This temporary table is then populated when the "insert into" statement is executed. Now this code by itself does not alert the DBA, although it does get the free space information for each drive on your SQL Server box into a temporary table where some T-SQL code can process it to alert the DBA.

The method I'm using to control the threshold alert process is a SQL Agent job that runs hourly. Each hour the SQL Server agent job collects the free space information for each SQL Server drive into a temporary table, such as the one above. Then for each drive I retrieve the free space information from this temporary table and compare it to a threshold I have set for each drive. If the amount of free space drops below the particular threshold setting for the drive, then I email the DBA using xp_sendmail. Here is a sample of some code that does just that.

declare @MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
select @MB_Free = MB_Free from #FreeSpace where Drive = 'C'
-- Free Space on C drive Less than Threshold
if @MB_Free < 1024
exec master.dbo.xp_sendmail
@recipients ='greg.larsen@netzero.net',
@subject ='SERVER X - Fresh Space Issue on C Drive',
@message = 'Free space on C Drive
has dropped below 1 gig'
select @MB_Free = MB_Free from #FreeSpace where Drive = 'F'
-- Free Space on F drive Less than Threshold
if @MB_Free < 2048
exec master.dbo.xp_sendmail
@recipients ='greg.larsen@netzero.net',
@subject ='SERVER X - Fresh Space Issue on C Drive',
@message = 'Free space on F Drive
has dropped below 2 gig'

This sample only checks the free space on two drives, but could easily be modified to handle additional drives. This sample code can either be placed directly into a SQL Agent job step, or can be built into a stored procedure that is executed by a job step. By building a SQL Server agent job to execute the above code, the DBA will be notified soon after a drive reaches its designated free space threshold. This alerting of low free space hopefully will allow the DBA time to resolve the free space problem before it becomes critical, and causes processes to fail. Note that the above code has a different free space threshold for each drive.

Another use of xp_fixeddrives might be to track disk space usage over time. Say you where to gather the free space information at regular intervals, for example weekly, and store it in a database table. This could be done using the approach above to populate the #FreeSpace temporary table. By gathering free space information over time and storing it in a permanent SQL Server table you will be able to produce a trend chart showing your disk space consumption overtime. By comparing the amount of free space between two points on the chart you will be able to determine the disk space consumed between those intervals. Having the rate of disk space consumption is valuable in tracking how fast your applications are consuming your available free disk space. This growth rate information is valuable when determining how much disk space is needed to support your applications for the next year.

Orignal link: http://www.databasejournal.com/features/mssql/article.php/3080501/Using-xpfixeddrives-to-Monitor-Free-Space.htm

Monitoring available disk space and growth rates are a couple of things a DBA should be performing. Without monitoring you run the risk of running out of space and causing critical problems for your application. If you have not been monitoring disk space availability and usage, then you might consider how you can use xp_fixeddrives to support your monitor needs.