Just Learned - sys.dm_os_volume_stats - Returns information about the operating system volume (directory) for databases files in SQL Server Den

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

sys.dm_os_volume_stats - Returns information about the operating system volume (directory) for databases files in SQL Server Den

Aug 5 2011 6:46AM by Paresh Prajapati

I just learned new DMF introduced in SQL Server 2011 CTP3, sys.dmosvolumestats which returns information about the operating system volume (directory) on which the specified databases and files are stored. The required argument for this DMV are databaseid, file_id accordingly. Let me run this DMF for the one system database data and log files.

SELECT 
database_id as DBID,file_id as FileId,volume_mount_point as VolumeMount,logical_volume_name as LogicalVolume,
file_system_type as SystemType,total_bytes as TotalBytes,available_bytes as AvailBytes,
is_read_only as [ReadOnly],is_compressed as Compressed 
FROM sys.dm_os_volume_stats(1,1)
UNION ALL
SELECT 
database_id as DBID,file_id as FileId,volume_mount_point as VolumeMount,logical_volume_name as LogicalVolume,
file_system_type as SystemType,total_bytes as TotalBytes,available_bytes as AvailBytes,
is_read_only as [ReadOnly],is_compressed as Compressed 
FROM sys.dm_os_volume_stats(1,2)
GO

/*
Output:
DBID FileId VolumeMount	LogicalVolume SystemType TotalBytes	  AvailBytes  ReadOnly Compressed
1    1		C:\			OS			  NTFS		 105804099584 30066728960 0		   0
1    2		C:\			OS			  NTFS		 105804099584 30066728960 0		   0
*/

2 Comments

Paresh: It's not a DMV, but a DMF (Dynamic Management Function) :)

commented on Aug 5 2011 9:15AM

Nakul Vachhrajani

4 · 33% · 10564

Thanks Nakul,

i have corrected in the post.

commented on Aug 10 2011 9:58PM

Paresh Prajapati

6 · 22% · 7044

Your Comment

Sign Up or Login to post a comment.

"sys.dm_os_volume_stats - Returns information about the operating system volume (directory) for databases files in SQL Server Den" rated 5 out of 5 by 5 readers +Paresh Prajapati