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 intcreate 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.
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.
