Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.

However, this only gives me an absolute number of MB free. What I really need is percentage free.

Is there another way in SQL Server 2005 to get this? If not, is there some other light-weight way to get it? If I can, I want to avoid installing a Java JRE, or Perl, or Python on my database server. Perhaps vbscript, or a small Windows executable on the file system?

Yes, I know I can Google this, and I have. It looks like there are a few ways to accomplish it, and I'm curious how my DBA brethren have handled this.

share|improve this question
1  
Why do you need this within SQL Server? There are many ways to get at the information - VBScript, PowerShell, WMI, C#, etc. None are really a slam dunk to run from within SQL Server. Can't you have a scheduled task that runs in (your favorite language) and updates a table? –  Aaron Bertrand Dec 19 '12 at 14:45

2 Answers 2

This can be done through PowerShell. See the below:

Get-PSDrive |
    Where-Object {$_.Provider -like "*FileSystem"} |
    Select-Object Name, Used, Free, @{Name = "Used_Percent"; expression = {$_.Used / ($_.Used + $_.Free) * 100}}

Or, through WMI:

Get-WmiObject -Class Win32_LogicalDisk |
    Select-Object DeviceID, VolumeName, @{Name = "Used_Percent"; expression = {($_.Size - $_.FreeSpace) / $_.Size * 100}}

Please see Aaron's comment to your question. I completely agree with him, this is not something that is easily done through SQL Server. You can finagle this to be run within SQL Server, but it's not necessarily a native way to get this information directly from SQL Server.

share|improve this answer
1  
While true (and +1), this doesn't really show how to get this information "in SQL Server"... –  Aaron Bertrand Dec 19 '12 at 14:55
    
@AaronBertrand I agree wholeheartedly, and I have noted that as well. –  Thomas Stringer Dec 19 '12 at 14:59

Nope no native method in 2005 (there are DMVs in 2012, dm_os_volume_stats), however this "hacky" xp_cmdshell might work..

http://www.mssqltips.com/sqlservertip/2444/script-to-get-available-and-free-disk-space-for-sql-server/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.