Invalid object name 'sys.dm_os_volume_stats'

Question

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?)

Answer 1

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.