Find Drive size and free space
-- This query will run SQL Server 2008 R2 only
SELECT distinct vs.volume_mount_point AS DriveName,
vs.total_bytes/1073741824 as TotalSize_GB,
vs.available_bytes/1073741824 AS FreeSpace_GB,
(vs.total_bytes/1073741824) -
(vs.available_bytes/1073741824) AS SpaceUsed_GB,
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,3)) * 100 AS [SpaceUsed %]
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point
SELECT distinct vs.volume_mount_point AS DriveName,
vs.total_bytes/1073741824 as TotalSize_GB,
vs.available_bytes/1073741824 AS FreeSpace_GB,
(vs.total_bytes/1073741824) -
(vs.available_bytes/1073741824) AS SpaceUsed_GB,
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,3)) * 100 AS [SpaceUsed %]
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point
