Ever wonder about the nitty-gritty details of where your SQL Server databases and files live? There's a handy dynamic management function in SQL Server that spills the beans on the operating system volume (directory) where your data resides. It's incredibly useful for checking out the attributes of your physical disk drives or getting a quick peek at the available free space in a specific 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);
This SQL query leverages the sys.dm_os_volume_stats function to give you a comprehensive overview, including the mount point, file system type, logical volume name, total size, available space, and even the percentage of free space. It also provides information on whether the volume supports compression, is compressed, or supports sparse files and alternate streams.
For more in-depth documentation on this powerful function, you can refer to the official Microsoft documentation: sys.dm_os_volume_stats (Transact-SQL)
