Compatible from SQL Server 2008
Returns information about space on disk drives used by databases in instance. This c an be handful in automation for example before creating or restoring new database. Also can be used in some type of reporting when collecting information from more SQL Server instances etc.
IF OBJECT_ID('sys.dm_os_volume_stats') IS NOT NULL
SELECT DISTINCT
vs.volume_mount_point AS VolumeMountPoint,
vs.file_system_type AS FileSystem,
vs.logical_volume_name AS LogicalVolumeName,
CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS TotalGB,
CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS UsedGB,
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS UsedSpacePct
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE);
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| VolumeMountPoint | nvarchar(512) | Mount point at which the volume is rooted. Can return an empty string. |
| FileSystem | nvarchar(512) | Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string |
| LogicalVolumeName | nvarchar(512) | Logical volume name. Can return an empty string |
| TotalGB | decimal(18,2) | Total size in GB of the volume. Cannot be null. |
| UsedGB | decimal(18,2) | Available free space in GB on the volume. Cannot be null. |
| UnusedSpacePct | decimal(18,2) | Percent of space in use on the volume. |