Archive

Posts Tagged ‘sys.dm_os_volums_stats’

SQL Server – New OS Related DMF – sys.dm_os_volume_stats

01.08.2011 No comments

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