• Article

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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sys.dm_os_volumne_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.

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.

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