Question:

How can I query SQL Server to get the drive space free, drive space used and total drive size.

Answer:

Beginning with SQL Server 2008R2 Microsoft introduced a new system DMF (dynamic management function) named sys.dm_os_volume_stats.  This DMF returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server. You can use this dynamic management function to check the attributes of the physical disk drive or return available free space information about the directory.

This DMF takes two arguments:

1. database_id – The ID of the database. database_id is int, with no default. Cannot be NULL.
2. file_id – The ID of the file. file_id is int, with no default. Cannot be NULL.

You can join this DMF to the sys.master_files DMV to get the drive space usage statistics for the drives where you have a database file stored.

-----------------------------------------

select distinct
convert(varchar(512), b.volume_mount_point) as [volume_mount_point]
, convert(varchar(512), b.logical_volume_name) as [logical_volume_name]
, convert(decimal(18,1), round(((convert(float, b.available_bytes) / convert(float, b.total_bytes)) * 100),1)) as [percent_free]
, convert(bigint, round(((b.available_bytes / 1024.0)/1024.0),0)) as [free_mb]
, convert(bigint, round(((b.total_bytes / 1024.0)/1024.0),0)) as [total_mb]
, convert(bigint, round((((b.total_bytes - b.available_bytes) / 1024.0)/1024.0),0)) as [used_mb]
from sys.master_files as [a]
CROSS APPLY sys.dm_os_volume_stats(a.database_id, a.[file_id]) as [b]

-----------------------------------------

Here are the results:

** One word of caution is that sys.dm_os_volume_stats will only produce stats for drives where a SQL Server database exists.

Another question: But… what if I am using SQL Server 2008 or older?

Another Answer: xp_fixeddrives

This extended stored procedure is not as useful regarding the information that it gives – only showing free space.  However, it DOES include all of the fixed drives on the server instead of just the drives where a SQL Server database exists.

------------------------------------

exec master.dbo.xp_fixeddrives

------------------------------------

Here are the results: