As I mentioned in my last post, I have started exploring new DMVs and some enhancements to existing DMVs in SQL Server 2012 DENALI. In my last post I talked about sys.dm_exec_query_stats
Today, I will talk about sys.dm_os_volume_stats – this DMV returns information about the operating system volume on which the specified databases and files are stored. Before I show a demo, let me clarify that this DMV is not new in SQL Server 2012 (DENALI). It is available in SQL Server 2008 R2 SP1 onwards, but not very well known.
The DMV accepts 2 arguments and cannot be null: database_id and file_id
For example; run the following query:
select DB_NAME(database_id) as DBName, file_id, volume_mount_point, logical_volume_name, total_bytes/1024/1024/1024 as total_bytes_GB, available_bytes/1024/1024/1024 available_bytes_GB from sys.dm_os_volume_stats(1,1)
And the output is self-explanatory :
I just used the master database and the mdf file as an example. You can use sys.master_files system catalog to find out the ids.