SQL Server Denali has many new interesting feature – one of the interesting feature is New DMVs.
This DMV returns information about the operating system volume (directory) on which the specified databases and files are stored. Here is the quick example I have created for the same.
SELECT DB_NAME(f.database_id) DatabaseName,
f.FILE_ID, size DBSize, file_system_type,
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);
Here is the screenshot of the same:
In the result set we can see the file system and volume database is mounted on as well database size.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
DMV’s are a neat feature, one of the challenges i have is keeping up with them. There is a lot of information that can be retrieved using DMV’s.
good
Hello Pinal,
This DMF it is already in SQL Server 2008 R2 SP1. BTW nice info.
Cheers,
Marcos Freccia
its giving an error that Invalid object name ‘sys.dm_os_volume_stats’.
can anybody help