3

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.

  • 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
3

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.

  • 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
0

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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