sys.dm_os_volume_stats (Transact-SQL)

APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics Parallel Data Warehouse

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.

Topic link icon 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.

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)