0

I am trying to select 15% of disk space available in a server.

 SELECT DISTINCT
            s.volume_mount_point [Drive],
            CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs],
           ((CAST(s.total_bytes / 1048576.0 as decimal(20,2)))*15)/100 AS [FifteenpercentAvailableMBs]  

            FROM 
                    sys.master_files f
                    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s

This query works well in Microsoft SQL Server 2008 R2 (SP2)

But it wont work in Microsoft SQL Server 2008 R2 (RTM)

How can I make this query work in 2008 R2 RTM? (I mean is there any alternative query that I can use to get 15% of available space?)

3
  • 3
    Why are you still running RTM? dm_os_volume_stats was introduced in SP1 May 11, 2015 at 15:38
  • we have some old servers we still need to support. May 11, 2015 at 15:44
  • 1
    That's not really a reason to still be using RTM May 11, 2015 at 15:44

1 Answer 1

7

You can't use this DMV in RTM; it was introduced in SP1 (though the current version of the documentation does not specify that, the 2008 R2 version does). You should be on Service Pack 3 anyway (that will make those old servers easier to support, not harder).

In RTM you can do some really convoluted things, like this. He is using xp_cmdshell to call PowerShell to grab output from a WMI class (Win32_Volume), then dump those results into a #temp table, and compare them to data from an undocumented and unsupported extended procedure, xp_fixeddrives.

There is a similar solution here. It uses WMI (Win32_PhysicalDisk), VBScript, and PowerShell from a SQL Server Agent job - while they avoid xp_cmdshell here, the overall methodology feels similarly hobbled together.

If you were working in construction, these would be the equivalent of about eight rolls of duct tape and a half gallon of WD-40.

Maybe you should consider performing this task using PowerShell or similar (doesn't this seem a lot simpler?), instead of trying to bend T-SQL to do this. SQL Server isn't really the right tool to manage server disk space.

1
  • I just signed up to vote you up ... "If you were working in construction, these would be the equivalent of about eight rolls of duct tape and a half gallon of WD-40." ... it made my day :)
    – Dan
    Sep 14, 2017 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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