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