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)
