Monitoring drive space is a critical task for DBAs and there are several scripts and articles written about this subject readily available on the internet. I’ve found these to be somewhat dated. Almost all of them use some combination of xp_fixeddrives, sysaltfiles, or sysfiles. My problem with these methods is that they face a questionable future. Xp_fixeddrives is an undocumented stored procedure and Microsoft could remove it from future versions of SQL Server at any time. Sysaltfiles and sysfiles were identified as depreciated with the release of SQL Server 2005 and exist as views today solely for backward compatibility. Their days are clearly numbered.

 I set out to develop a routine that would do three things:

1. Gather disk usage information using version-appropriate system tables for the version of SQL Server that is running on a particular server
2. Gather this information across multiple servers in our network
3. Store the data in a table for reporting

My routine is designed to run on a SQL 2008 server (although it can monitor 2005 and 2000 servers). It also uses a Powershell 1.0 compatible script. However, if you do not have Powershell installed, or do not wish to track the total size of your drives, the Powershell portion can be omitted.

The Data Gathered

I started with Jeremiah Peschka’s routine, found here, and modified it slightly. My routine gathers the following data:

• Server name
• Database name
• Physical filenames for the database, including drive and path
• Size of database
• Maximum size of disk database files are stored on

In today’s world, it is common for SQL Servers to be running on virtual machines or to be using SANs for storage. Both of these scenarios enable easy addition of more drive space. For that reason, I wanted to collect the maximum disk size, as that can change more readily now than in the past. I also will be recording the date the size data was captured, to allow for monitoring changes over time.

The Layout

The attached zipfile contains three files. DiskStatsTable.sql contains a script to create the following tables:

• DatabaseDiskStats – this is the table that will store the actual data
• ServerToCheckDiskStats – this table contains a list of servers to check, along with their SQL version and two additional flags, described later.

The tables below describe the layout of these two objects:

Table 1 - DatabaseDiskStats

Table 2 - ServersToCheckDiskStats

The table structure is denormalized – the server name is stored in both tables. This is to simplify reporting later. With the server name stored the first table, no joins will be required when reporting.

The second file, GatherServerDiskUsageData.sql, contains the stored procedure used to gather the data. You’ll need to modify the first two SET statements to use the username and password you have chosen to use.

The last file, DBDiskSpaceAnalysis.ps1, is a Powerscript shell that is used to get the total drive size of the drives being monitored. If you do not want to monitor this piece of information, you do not need to run it. If you do use this, you’ll need to modify first two lines of the script as follows: $DiskStatsServer should be set to the name of the SQL Server you are running this routine on and $DiskStatsDB should be set to the name of the database the above two tables are in on that server.

In addition to the files, the routine requires a SQL user account to be created on all SQL Servers that are to be checked. The routine assumes a SQL login of “DiskStatsUser” and a password of “password.” Both of these are configurable, however. Details on the permissions this user will need are below.

Implementation

Run the scripts to create the tables and stored procedure. If you have not done so already, create the DiskStatsUser on all the servers you want to monitor. This user needs the following permissions and access:

• For 2005 and 2008 servers, it needs to be a member of the public role and be granted the VIEW ANY DEFINITION right (“GRANT VIEW ANY DEFINITION TO DiskStatsUser”)
• For 2000 servers, it needs access to the master and tempdb databases and should be a member of the public role.
• For 2000 servers, it needs to be granted SELECT permission on master.dbo.sysaltfiles. NOTE: This may be a security risk, depending on how secure your company wants to be. By default, there are no permissions granted to the sysaltfiles table, so only sysadmins have access.

Additionally, if you want to track the total drive space, you will need to allow the Windows account that SQL Server Agent runs under access to your other servers in order for the Powershell script to connect to those servers and get drive information.

As an overview, the entire process works like this:

• The list of servers to monitor is read from the ServersToCheckDiskStats table and looped through.
• A linked server entry is created to the server being monitored.
• Data is gathered from the linked server.
• If the linked server is not to be persisted, the linked server entry is deleted.
• A Powershell script is run to gather the total drive size of the drives found in the above steps.

A more detailed explanation of each step follows.

The next step in setting up the routine is to populate the ServersToCheckDiskStats table. The servername field should contain the network name of the SQL Server you want to monitor. The PersistLink flag should be set appropriately.

Data is gathered from the various SQL Servers by creating a linked server entry to that server. If you do not want that linked server entry to remain after the routine is finished, set this flag to False or 0. If you do want the linked server to remain, or if this server is already configured as a linked server (with a user that has the necessary permissions for this routine), set this flag to True or 1.

The SQLServerVersion field should be filled in as either “2008”, “2005”, or “2000”, depending on the version of SQL running on the monitored server. Values other than those will be flagged as errors in the data table, but will not stop the routine from running.

TrackTotalDiskSpace is a flag that specifies if the total disk size of drives on the server is to be checked by the Powershell script. The default is 1, meaning they will be checked. In some instances, you may not want to do this, so you would set this to 0. For instance, I have a server that is in an untrusted domain, so the Powershell script cannot gather this information due to permission issues (although the information gathered by the stored procedure can still be collected). Therefore, I’ve disabled the check for this particular server. Note that if the check is disabled, the value in DatabaseDiskStats.MaxDriveSize_in_MB will be set to 0.

The final step before setting up a job to implement this is to place the Powershell script in a location that can be accessed by the SQL Server Agent network account.

Scheduling The Task

To start collecting data, create a job that runs daily. (Or less frequently, depending on your needs. If you want to gather data more often than once a day, you should change the default value of the DatabaseDiskStats.StatDate column to include the time portion of the current day.)

The first step in the job should execute the stored procedure GatherServerDiskUsageData. The second step should execute the Powershell script. A final (optional) maintenance step would be to delete any entries in DatabaseDiskStats that are older than X days. For example, I only want to store 6 months of data, so my third step consists of the command “DELETE FROM DatabaseDiskStats WHERE StatDate  < DATEADD(d,-180,getdate())”

Notes About The Stored Procedure

The stored procedure is where all the work is done and there are a few comments I’d like to make about it.

Although SQL 2005 and 2008 use the same system tables to store database size information, I wrote the code to handle them separately for clarity.

Tempdb has to be handled differently than the other databases. The filesize values for this database that are stored in sys.master_files are not the current size of the database files. Rather, they are the size the files will be created with when SQL Server restarts. (Recall tempdb is recreated each time SQL Server starts.) The actual filesizes are found in the sys.database_files table in the tempdb database itself. A similar situation occurs with SQL 2000 and its system tables. This is a fact that many of the scripts I found on the internet fail to account for.

Using this routine to monitor SQL Server’s disk usage across all the servers in your network will ensure you are using the latest and greatest system tables for your monitoring and do not need to worry about upgrades causing your monitoring to break due to obsolete system tables.