Hello. Quick post today. I needed to find the total disk space for a remote server with access only to T-SQL. No PowerShell or file system sneak-peek was allowed. Actually I would send my queries to someone else run it, and only then I could look onto the results.
So starting of this post from Pinal Dave and this MSDN article I came up with a quick query. It’s not exactly elegant, but gets the job done. SQL 2008 R2+ code ahead.
SELECT distinct(volume_mount_point), total_bytes/1048576 as Size_in_MB, available_bytes/1048576 as Free_in_MB FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) group by volume_mount_point, total_bytes/1048576, available_bytes/1048576 order by 1
This solution was also posted on SQLServerCentral forums. That is a great place to search for such things.
Also get some familiarity with sys.dm_os_sys_info and EXEC xp_msver; – those can be of great help in such dire situations.
cya!
The Lonely DBA 
Thank you for this query. Unfortunately, this query returns disk space informations about volumes that host database files. If a server have a volume that don’t host a database file, the query don’t dispay any information for that volume.
Hello. Thats a good point. Unfortunately I’m not able to search for this improvement, and if the DB servers are dedicated (as they should, most of the time) this will not be a big issue. But thanks for pointing it out!