sys.dm_os_volume_stats (Transact-SQL)
Applies to:
SQL Server
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.
Transact-SQL syntax conventions
Syntax
sys.dm_os_volume_stats (database_id, file_id)
Arguments
database_id
ID of the database. database_id is int, with no default. Cannot be NULL.
file_id
ID of the file. file_id is int, with no default. Cannot be NULL.
Table Returned
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. Returns null on Linux operating system. |
volume_id | nvarchar(512) | Operating system volume ID. Can return an empty string. Returns null on Linux operating system. |
logical_volume_name | nvarchar(512) | Logical volume name. Can return an empty string. Returns null on Linux operating system. |
file_system_type | nvarchar(512) | Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string. Returns null on Linux operating system. |
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 | tinyint | Indicates if the volume supports operating system compression. Cannot be null on Windows and returns null on Linux operating system. |
supports_alternate_streams | tinyint | Indicates if the volume supports alternate streams. Cannot be null on Windows and returns null on Linux operating system. |
supports_sparse_files | tinyint | Indicates if the volume supports sparse files. Cannot be null on Windows and returns null on Linux operating system. |
is_read_only | tinyint | Indicates if the volume is currently marked as read only. Cannot be null. |
is_compressed | tinyint | Indicates if this volume is currently compressed. Cannot be null on Windows and returns null on Linux operating system. |
incurs_seek_penalty | tinyint | Indicates the type of storage supporting this volume. Possible values are: 0: No seek penalty on this volume, typically when the storage device is PMM or SSD 1: Seek penalty on this volume, typically when the storage device is HDD 2: The storage type can't be determined when the volume is on a UNC path or mounted shares NULL: The storage type can't be determined on Linux operating system Applies to: SQL Server (starting with SQL Server 2019 (15.x)) |
Security
Permissions
Requires VIEW SERVER STATE
permission.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
Examples
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);
See also
sys.master_files (Transact-SQL)
sys.database_files (Transact-SQL)