Monitoring the size of data files and available disk space on each of the drives (where the data files are placed), is one of the key activities of every database administrator. Depending upon the environment and requirements, people rely on different methods/tools to get this information.

xp_fixeddrives

This (undocumented?) extended stored procedure returns information about the free space available in each of the fixed drives on the server. However, using this type of stored procedures always a pain because they deny the freedom of using the output in a query. If you are writing a piece of TSQL code that utilizes the output of the procedure (such as sending a notification email when the available disk space falls below a threshold value) you will need to capture the output into a table and then write your actual query that uses the information produced by the stored procedure.

In addition, the only piece of information xp_fixeddrives gives us is the free space. One may want to find out the total disk size and many other related pieces of information, which unfortunately, is not available easily.

The following example retrieves information about available free space using xp_fixeddrives

DECLARE @t TABLE (
	Name NVARCHAR(50),
	FreeSpace INT 
)

INSERT INTO @t
EXEC xp_fixeddrives

SELECT
	df.physical_name AS DataFile,
	t.name AS Drive,
	CAST(t.FreeSpace/1024 AS VARCHAR) + ' GB' AS FreeSpace
FROM @t t
INNER JOIN sys.database_files df ON 
	t.name = LEFT(physical_name,1)
	
/*
DataFile          Drive FreeSpace
----------------- ----- ----------
C:\DATA\br3.mdf   C     200 GB
D:\DATA\br3_2.LDF D     123 GB
E:\DATA\br3_1.ndf E     145 GB
*/	

Enter sys.dm_os_volume_stats!

SQL Server 2008 R2 SP1 introduces a really cool Dynamic Management Function that exposes several attributes of the physical disk drives. Some of the interesting pieces of information returned by this function are:

1. Mount point at which the volume is rooted.
2. Logical volume name
3. Type of file system volume, such as NTFS
4. Total size in bytes of the volume
5. Available free space on the volume
6. Whether volume supports operating system compression or not
7. Whether the volume supports alternate streams or not
8. Whether the volume supports sparse files or not
9. Whether the volume is currently marked as read only or not
10. Whether the volume is currently compressed or not

The query we saw earlier can be easily re-written using this new function as follows.

SELECT 
	physical_name AS DataFile,
	size AS FileSize,
	volume_mount_point AS Drive,
	CAST(total_bytes/1024/1024/1024 AS VARCHAR) + ' GB' AS DriveSize,
	CAST(available_bytes/1024/1024/ 1024 AS VARCHAR) + ' GB'  AS SpaceAvailable
FROM sys.database_files df
CROSS APPLY sys.dm_os_volume_stats(db_id(), df.file_id) ovs

/*
DataFile          FileSize Drive DriveSize SpaceAvailable
----------------- -------- --------------  --------------
C:\DATA\br3.mdf   1166296  C:\   248 GB    200 GB
D:\DATA\br3_2.LDF 254144   D:\   248 GB    123 GB
E:\DATA\br3_1.ndf 128      E:\   248 GB    145 GB
*/

Reference: http://msdn.microsoft.com/en-us/library/hh223223(v=sql.105).aspx