Returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server. Use this dynamic management function to check the attributes of the physical disk drive or return available free space information about the directory.
SELECT DISTINCT vs.volume_mount_point ,vs.file_system_type ,vs.logical_volume_name ,CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)] ,CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)] ,CONVERT(DECIMAL(18, 2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %] ,vs.supports_compression ,vs.is_compressed ,vs.supports_sparse_files ,vs.supports_alternate_streams FROM sys.master_files AS f WITH (NOLOCK) CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs ORDER BY vs.volume_mount_point OPTION (RECOMPILE);
