Home > Management Views and Functions, SQLServer, SQLServer 2008 R2, SQLServer 2012 > SQL Server – New OS Related DMF – sys.dm_os_volume_stats

SQL Server – New OS Related DMF – sys.dm_os_volume_stats

sys.dm_os_volume_stats is a new dynamic management function introduced in SQL Server 2008 R2 SP1 and Denali CTP3. It returns information about partitions on which database files reside.

You can now check for free space on a particular partition, instead of using xp_fixeddrives which does not support this.

sys.dm_os_volume_stats takes two parameters databae_id and file_id,

          sys.dm_os_volums_stats(@Database_ID, @File_ID)

For example, you can use per-database view sys.database_files to get the File_ID, and pass the same:

SELECT      File_ID, Name, Physical_Name

FROM        sys.database_files

Result Set:

File_ID       Name                        Physical_Name

———-    ———–                 ———————

1             AdventureWorks2008R2_Data   C:\…08R2_Data.mdf

2             AdventureWorks2008R2_Log    C:\…08R2_Log.ldf

65537         FileStreamDocuments2008R2   C:\…ents2008R2

 

(3 row(s) affected)

You can pass this to sys.dm_os_volume_state to get information for a particular partition as follows:

SELECT      STATS.Volume_Mount_Point AS 'Drive',

            DB_NAME(STATS.Database_ID) AS 'Database Name',

            FILES.[Name] AS 'File Name',

            FILES.[Size] / 128 AS 'File Size',

            ((STATS.Total_Bytes / 1024) / 1024)

            AS 'Drive Capacity (MB)',

            ((STATS.Available_Bytes / 1024) / 1024)

            AS 'Drive Free Space (MB)'

FROM  sys.database_files FILES

CROSS APPLY sys.dm_os_volume_stats

            (DB_ID(N'AdventureWorks2008R2'), FILES.file_id) STATS

Result Set:

Drive  Database Name File Name     File Size     Drive Capacity (MB)  Drive Free Space (MB)

—— ————- ———     ————  ——————-  ———————

C:\    AdventureWorks2008R2 AdventureWorks2008R2_Data  196    145456 57513

C:\    AdventureWorks2008R2 AdventureWorks2008R2_Log   18     145456 57513

C:\    AdventureWorks2008R2 FileStreamDocuments2008R2  0      145456 57513

 

(3 row(s) affected)

 

Above example returns the information for a single database, to get the information for all databases on the server, you need to apply sys.db_os_volume_stats to output from sys.master_files which is a system-wide view:

— For All Databases

 

SELECT      STATS.Volume_Mount_Point AS 'Drive',

            DB_NAME(STATS.Database_ID) AS 'Database Name',

            FILES.[Name] AS 'File Name',

            FILES.[Size] / 128 AS 'File Size',

            ((STATS.Total_Bytes / 1024) / 1024)

            AS 'Drive Capacity (MB)',

            ((STATS.Available_Bytes / 1024) / 1024)

            AS 'Drive Free Space (MB)'

FROM  sys.master_files FILES

CROSS APPLY

            sys.dm_os_volume_stats

            (FILES.database_id, FILES.file_id) STATS

Partial Result Set:

Drive  Database Name File Name     File Size     Drive Capacity (MB)  Drive Free Space (MB)

—— ————- ———-    ———–   ——————-  ———————

C:\    master        master        4             145456               57201

C:\    master        mastlog       0             145456               57201

C:\    model         modeldev      2             145456               57201

C:\    model         modellog      9             145456               57201

C:\    SqlAndMe      SqlAndMe      3             145456               57201

C:\    SqlAndMe      SqlAndMe_log  10            145456               57201

C:\    TempDatabase  TempDatabase  6             145456               57201

C:\    PartitionDB   PartitionDB   3             145456               57201

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: