SQL Server 2012 DENALI new DMV- sys.dm_os_volume_stats

Dear All,

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,
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.



Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook


Leave a Reply

Your email address will not be published.