Get disk space percentage free with SQL Server 2005?

Question

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.

Answer 1

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.

Answer 2

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/