The sys.dm_os_volume_stats Dynamic Management Function is used to return information about disk volumes that contain your database files. This is a function so you have to pass in a database_id and a file_id. To better illustrate this function, I will run the below query on my TRAINING instance.
SELECT db_name(vs.database_id) AS DatabaseName, vs.file_id, vs.volume_mount_point, vs.volume_id, vs.logical_volume_name,
vs.file_system_type, (vs.total_bytes/1024/1024/1024) AS [TotalSize(GB)], (vs.available_bytes/1024/1024/1024) AS [AvailableSize(GB)],
vs.supports_compression, vs.supports_alternate_streams, vs.supports_sparse_files, vs.is_read_only, vs.is_compressed
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE db_name(vs.database_id) like 'Adventure%'
In the query above, I used the information from sys.master_files and then used the CROSS APPLY with my sys.dm_os_volume_stats function to return the shown result sets. In addition to this, I filtered the results so we only se information for the ‘AdventureWorks2012’ and ‘AdventureWorksDW2012’ databases.
The first column returned by this function displays the Database Name. The second column displays the file id which in my case is the data file and transaction log file. The volume_mount_point column represents the the mount point that the volume is rooted to. Next we have the id for the specific volume and a name for the volume. The file_system_type column signifies whether you are using NTFS, FAT, RAW, etc system file types. The next columns represent the size of my volume and how much of that volume is still available for use. The next three columns of this function are self explanatory for the most part, signifying whether or not compression, alternate streams, or sparse files are supported. Lastly, we have have columns displaying whether or not the volume is read only or if the volume is compressed.
Many DBA’s tend to rely on the extended stored procedure xp_fixeddrives when needing to see free size information about the various drives on a particular server. The problem with disk drive letters is that you are limited by the number of letters in the alphabet. If your database servers use mount points instead of drive letters, then xp_fixeddrives becomes useless to you at that point. This is when I find a Dynamic Management Function like this one to be extremely useful. There are other ways to get mount point information such as using powershell and WMI, but for a SQL Server solution, this DMF provides one of the best solutions available.
For more information about this Dynamic Management Function, see the below Books Online link:
Follow me on Twitter @PrimeTimeDBA