Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

Getting more details with an enhanced xp_fixeddrives for SQL Server


By:   |   Last Updated: 2013-09-11   |   Comments (22)   |   Related Tips: 1 | 2 | More > Database Administration

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

sp_fixeddrives

xp_fixeddrives
sp_fixeddrives
 

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.

exec sp_fixeddrives 1
You are more then welcome to change the parameter like "exec sp_fixeddrives "DBMode" or something..

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.


Last Updated: 2013-09-11


next webcast button


next tip button



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, November 18, 2018 - 10:13:35 AM - Varun Deshpande Back To Top

 I got the error

Msg 8114, Level 16, State 5, Procedure sp_fixeddrives, Line 71

Error converting data type varchar to real.

This was because the volumes were mounted with a mount point that had a space in it.

Example D:\B Drive\ where "B Drive" was a volume mount point mounted withing the D: drive.

To fix this I made the below changes to the SP on lie number 72 ad 75 (replaced ' ' with '\ ')

line 72: SET @Drive = LEFT(LTRIM(@STRLine),CHARINDEX('\ ',LTRIM(@STRLine)))

line 75: SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX('\ ',LTRIM(@STRLine))),'')))


Thursday, July 14, 2016 - 9:52:31 AM - George Brown Back To Top

 Justiun,

You probably want to break down the procedure into chunks to determine exactly where the error is occuring.  You might also want to execute the WMIC commands outside of the stored proc to make sure they are returning the results you expect. 

 The first command "wmic volume where drivetype="3" get caption, freespace, capacity, label" should return something similar to this:

Capacity             Caption                                                                          FreeSpace     Label
366997504         \\?\Volume{a468f02c-534d-11e5-80dd-806e6f6e6963}\  142811136        System Reserved
255691059200   C:\                                                                                121523228672  Windows


Thursday, July 14, 2016 - 9:39:14 AM - Justiun Back To Top

 Has anyone figured out a fix for this issue?

 

Msg 8114, Level 16, State 5, Procedure sp_fixeddrives, Line 72 [Batch Start Line 0]

Error converting data type varchar to real.

 


Friday, July 08, 2016 - 1:30:40 PM - George Brown Back To Top

Just wanted to add a note in case anyone runs into the same error I found while executing this code on a workstation.  

You may receive an error message:

"Msg 537, Level 16, State 2, Procedure sp_fixeddrives, Line 131

Invalid length parameter passed to the LEFT or SUBSTRING function.

The statement has been terminated."

 

This occurs in the section used to resolve issues with long volume names on Server 2003.  If RPC services are not running on the workstation or server where the code is being executed.  You can see this if you execute the WMIC call manually.  This is the returned message (where XXXXX is your workstation or server name):

 

"Node - XXXXX

RPC Server not available.

 

Node,"

 

I assume that you can correct this by ensuring the RPC services are properly running.  However I chose to just add a section to the code that will delete all records from the #output1 table if the text "RPC Server not available." is found in the output.  Something like this:

 

IF EXISTS (select 'x' from #output1 where col1 like '%RPC Server not available.%')

begin

 

delete 

from #output1

 

end

 

Just figured I would pass along in case anyone else ran into this error.  


Monday, March 02, 2015 - 1:52:49 PM - Isaiah Back To Top

Thanks for this code. I ran it after turning on cmd shell as advised. 

Sincerely,

Isaiah.


Tuesday, February 03, 2015 - 6:00:26 AM - Chelladurai Back To Top

Dear Sir,

 

I tried this command and it is not showing the capactity / freespace and Label for few drives. Whereas if I run the same command in dos prompt (

wmic logicaldisk get size,freespace,caption

 

) I am getting the drive space. Can you please help on this issue.

 

Thanks in Advance

Chelladurai


Friday, January 16, 2015 - 9:46:52 AM - Sachin Back To Top

Hello All,

 

I have used the SP_fixeddrives code it is very useful however the Sp_fixeddrives 1 wont give the information of the database present in the Mountpoint ...So if we can see the data present in the Mount point too that would be great.


Saturday, July 26, 2014 - 1:13:48 PM - Wilfred van Dijk Back To Top

If you get the error (converting data type varchar to real) that's probably because you're running it on Windows 8? (in my case, yes)

Try running this statement and see if it's getting output:  EXEC xp_cmdshell 'wmic volume where drivetype="3" get caption, freespace, capacity, label'

and you'll get: 'wmic' is not recognized as an internal or external command,
 
 

Monday, June 09, 2014 - 2:47:48 PM - Justiun Back To Top

Same error here

 

Msg 8114, Level 16, State 5, Procedure sp_fixeddrives, Line 71

 

Error converting data type varchar to real.


Wednesday, March 26, 2014 - 10:52:25 AM - Hemant Back To Top

Hi Kun,

 

I am getting follwoing error while executing  "Exec dbo.sp_fixeddrives 1"

Msg 8114, Level 16, State 5, Procedure sp_fixeddrives, Line 71

Error converting data type varchar to real.


Wednesday, March 19, 2014 - 1:07:54 PM - Rick Willemain Back To Top

Thanks !


Thursday, December 05, 2013 - 4:38:51 PM - Bob St. Aubyn Back To Top

Sean's comments might be considered to be a bit gruff by some ;-), but I happen to agree with him.  To disqualify any feature available to SQL Server because the general scuttlebutt says it's "bad" is to me just as foolish as blindly implementing some new feature because your favorite source says it's all the rage.  What's really important is to fully understand how something works and the conditions under which it might be bad or good.

I've seen dozens of cursors in production code.  Same goes for DML triggers, and I hate them both for what I've seen them do to performance on a well-loaded OLTP instance.  But I don't want to have them removed from the product!  I 'hate' them because they are so misunderstood and so often abused.  Everything has its place, and there are rare situations where a cursor in a stored proc or a table trigger might be the best solution.

As far as Kun's suggestion I think it's pretty resourceful.  I use something very similar to temporarily enable xp_cmdshell while I make a wmic call out to get drive properties while I'm running the nightly maintenance job.  Since it's run by the agent service there's no problem turning it on, and as soon as wmic returns the data I flip it back off again.

If all of your instances are 2012 then sure, you should make use of sys.dm_os_volume_stats.  But if not, and you're trying to make something that is going to work across the board you'll have to consider other options.  Don't fear the features!  (Nice job Kun)


Wednesday, November 20, 2013 - 12:46:28 AM - Arjun Back To Top

Really a Nice solution. !!

How to display the Cluster Disk number assigned to this MOUNT \ LUN..

For example a MOUNT dirve letter name is K and inside it has FOLDER1,FOLDER2.  Whare as in the cluster storage the names given to this drives are ClusterDisk12  , ClusterDisk13.

How to display this ClusterDisk12  , ClusterDisk13 name in the result output.

Thanks,

Arjun

 


Thursday, November 14, 2013 - 12:05:39 PM - Sean McCown Back To Top

I'm getting so sick of hearing people spit out that cmdshell is dangerous if you don't handle it right.  Then handle it right and shut up already.  There are many things that are dangerous if you don't handle them right and cmdshell is no different.  It just got a bad rep because it was enabled by default for so many years, but not it's not.  Just lock it down and use it.  And if you're afraid of it then use the powershell solution.

And if you really want to do this in sql then take the same concept and throw it into a CLR sp.  Of course that brings another set of problems that you don't want to get into if you're still afraid of cmdshell.

So in short, don't cut yourself out of useful solutions like this because you're too lazy to do a little reading.


Tuesday, October 15, 2013 - 5:25:52 PM - Chuck Hottle Back To Top

Nice.  We use a VB script to do this but I like this approach.


Thursday, September 12, 2013 - 9:37:46 AM - Kun Lee Back To Top

Timothy, I couldn't agree more.. I always have problem with xp_cmdshell but so hard not to use for certain cases. However, you got me thinking that I can modify the proc to if it is SQL 2008 or lower, use xp_cmdshell but if it is SQL 2008 R2 SP1 or higher to use DMV? I don't have much of R2 yet but at least good start to have. I don't think it provide the mount point. I can do some more testing on SQL 2012 as it should but need to confirm.

 

Erick, thank you for comments!! I wasn't even sure if I should use "sp_" though as it is mostly used by MSFT so I was worry about conflicting the name when they come up with upgraded version. :)


Thursday, September 12, 2013 - 9:16:13 AM - Erik Back To Top

Nice solution, I would recommend changing the name to xp_fixeddrives2 or xp_fixeddrives_deluxe or something of that nature to keep with the naming convention for stored procedures that access system resources though.

Thanks for the solution either way.


Wednesday, September 11, 2013 - 6:16:09 PM - TimothyAWiseman Back To Top

I rather like this solution.  However, enabling xp_cmdshell can have serious security implications if not handled carefully.  That may cause issues with using this on some servers.


Wednesday, September 11, 2013 - 2:16:55 PM - Kun Lee Back To Top

Sean, I agree on PowerShell hanles this problem much more elegantly and I am also using that too. For some reason, I found this is quick and dirty way to identify issues and can be used with multi server query for other benefits. Still, I love powershell. 

 

Paul, this is just a code that you can change for your need anytime. I also think the same way but also I just like having less typing :)

 

Brain, unfortunatly, we have many SQL 2000 server so I didn't have that choice. Good commen Brian!!

 

Again, thank you all for great feedback!


Wednesday, September 11, 2013 - 12:30:24 PM - Brian Back To Top

Won't the dm_OS_Volume_Stats also give you mount point info? Granted this will limit you to 2012. But, you could avoid needing to use powershel.

Something like below would also get your filegroup info:

 

FROMsys.master_filesmf

 

CROSSAPPLYsys.dm_os_volume_stats(mf.database_id,mf.FILE_ID)


Wednesday, September 11, 2013 - 11:47:36 AM - Paul Back To Top

Using a parameter with the name of format in an sp working with fixed drives is perhaps not the best choice....

Just saying... :-0


Wednesday, September 11, 2013 - 9:13:07 AM - Sean McCown Back To Top

Powershell handles this problem much more elegantly.  the code is small and you don't have to deploy it to any servers.  You can query any server from your workstation.  Create yourself a function and just pass in the server name.

And if you wanted to stick with your method so you have it in SSMS, then call powershell from xp_cmdshell and then you can still pass in the servername as a param.  This way you can have it available from t-sql and still query any box you like.

The basics of the command are as follows:

gwmi win32_volume -computername server1 | FT Name, FreeSpace, Capacity -auto

You can get very sophisticated with it too.  You can include/exclude different types of drives, you can do the math and convert to different formats, etc.  It's easy and it's a lot more flexible than just xp_cmdshell alone.


Learn more about SQL Server tools