sys.dm_os_volume_stats (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel 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

sys.dm_os_volume_stats (database_id, file_id)  

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.

ColumnData typeDescription
database_idintID of the database. Cannot be null.
file_idintID of the file. Cannot be null.
volume_mount_pointnvarchar(512)Mount point at which the volume is rooted. Can return an empty string.
volume_idnvarchar(512)Operating system volume ID. Can return an empty string
logical_volume_namenvarchar(512)Logical volume name. Can return an empty string
file_system_typenvarchar(512)Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string
total_bytesbigintTotal size in bytes of the volume. Cannot be null.
available_bytesbigintAvailable free space on the volume. Cannot be null.
supports_compressionbitIndicates if the volume supports operating system compression. Cannot be null.
supports_alternate_streamsbitIndicates if the volume supports alternate streams. Cannot be null.
supports_sparse_filesbitIndicates if the volume supports sparse files. Cannot be null.
is_read_onlybitIndicates if the volume is currently marked as read only. Cannot be null.
is_compressedbitIndicates if this volume is currently compressed. Cannot be null.

Permissions

Requires VIEW SERVER STATE permission.

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);  

sys.master_files (Transact-SQL)
sys.database_files (Transact-SQL)

Community Additions

ADD
Show: