Getting more details with an enhanced xp_fixeddrives for SQL Server

Problem

I manage a large number of SQL servers and I don’t want to waste SAN space, so I want to control SQL server drive space very closely. Because of that, when there is a space problem, I use xp_fixeddrives to see how much free space the drives have but this extended stored procedure doesn’t give all the information that I need. Also, we are starting to use Mount Points more and I want to see free space by mount point as well.

Solution

To get the necessary information, I wrote a stored procedure called sp_fixeddrives and deployed to the master database. The reason that I deployed it to master is that it is easier to access it like xp_fixeddrives by just changing “x” to “s”.

Here are the requirements when I wrote the procedure:

1. Total disk size information is required
2. Volume name needs to be shown
3. Mount point information is required
4. Keep the same column names unless there are new columns
5. Show disk and database information
6. Show database recovery option to help troubleshoot abnormal transaction log file sizes

You can download sp_fixeddrives here.

Pre-requisites

1. xp_cmdshell must be turned on as the procedure uses this feature
2. WMI scripting must be turned on
3. Windows 2003 or later (has been tested on these versions)

Compare the SQL Server xp_fixeddrives vs. sp_fixeddrives commands

The key difference is that my stored procedure shows two mount points. You can see that MB free and MB TotalSize is different for the mount points.

xp_fixeddrives output

sp_fixeddrives output

SQL Server Database Detail Mode – “exec sp_fixeddrives 1”

When there is a space problem, you want to know which databases are located on which volumes and how much free space as well as the total size of the volumes.

Here is a sample run. Since it returns many columns, I broke this into two images for easier reading.

Some of the code from the procedure

I won’t go into much detail about the code inside the procedure, but I wanted to show a few lines of code to explain how the data was retrieved.

First, here is a simple WMI script that I ran to grab the volume names, free space, total size (capacity) and volume name. This is called using xp_cmdshell. This data is then loaded into a temp table and used later in the final result set.

INSERT INTO #DrvLetter
EXEC xp_cmdshell 'wmic volume where drivetype="3" get caption, freespace, capacity, label'

Second, when one of my colleagues, Vara Thelu, tested the procedure he found out that if volume name is longer then a certain number of characters on Windows 2003 server (Windows 2008 isn’t problem), WMI script doesn’t provide the volume name properly and returns an empty string. He recommended using another WMI script which is shown below. Please keep in mind that the below script should be just one line, this was changed just to make it easier to read.

SET @SQL ='wmic /FailFast:ON logicaldisk 
where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") 
get deviceid,volumename /Format:csv'

Next Steps

• I hope this simple stored procedure helps you diagnose space issues or helps with better capacity planning.
• My company has Integrated Development, Integrated Test and Production servers. One great option for this stored procedure would be to have the ability to compare two SQL servers, but I haven’t had a chance to write that one yet.
• You can download sp_fixeddrives here.