.png)
sys.dm_os_volume_stats (Transact-SQL)
Returns information about the operating system volume (directory) on which the specified databases and files are stored. Use this dynamic management function in SQL Server 2008 R2 SP1 and later versions to check the attributes of the physical disk drive or return available free space information about the directory.
Column | Data type | Description |
database_id | int | ID of the database. Cannot be null. |
file_id | int | ID of the file. Cannot be null. |
volume_mount_point | nvarchar(512) | Mount point at which the volume is rooted. Can return an empty string. |
volume_id | nvarchar(512) | Operating system volume ID. Can return an empty string |
logical_volume_name | nvarchar(512) | Logical volume name. Can return an empty string |
file_system_type | nvarchar(512) | Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string |
total_bytes | bigint | Total size in bytes of the volume. Cannot be null. |
available_bytes | bigint | Available free space on the volume. Cannot be null. |
supports_compression | bit | Indicates if the volume supports operating system compression. Cannot be null. |
supports_alternate_streams | bit | Indicates if the volume supports alternate streams. Cannot be null. |
supports_sparse_files | bit | Indicates if the volume supports sparse files. Cannot be null. |
is_read_only | bit | Indicates if the volume is currently marked as read only. Cannot be null. |
is_compressed | bit | Indicates if this volume is currently compressed. Cannot be null. |
A. Return total space and available space for all database files
The following example returns the total space and available space (in bytes) for all database files in the instance of SQL Server.
SELECT f.database_id, f.file_id, volume_mount_point, total_bytes, available_bytes FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
B. Return total space and available space for the current database
The following example returns the total space and available space (in bytes) for the database files in the current database.
SELECT database_id, f.file_id, volume_mount_point, total_bytes, available_bytes FROM sys.database_files AS f CROSS APPLY sys.dm_os_volume_stats(DB_ID(f.name), f.file_id);
