I have always wanted to know the disk size and free space available using TSQL and not use xp_cmdshell. I have hated to use xp_fixeddrives since it only gives the free space and not the total size of the volume. Just learnt about the DMV: sys.dm_os_volume_stats and works great. This works only on SQL Server 2008 R2 SP1 and higher though.
SELECT DISTINCT volume_mount_point as Drive,
logical_volume_name as Volume_Name,
CAST(total_bytes/1024./1024./1024. as decimal(20,2))
as Total_Size_GB,
CAST(available_bytes/1024./1024./1024. as decimal(20,2))
as Free_Space_GB,
CAST((CAST(available_bytes as decimal(20,2))*100/total_bytes) as decimal(5,2))
as Percent_Free
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
ORDER BY 1
logical_volume_name as Volume_Name,
CAST(total_bytes/1024./1024./1024. as decimal(20,2))
as Total_Size_GB,
CAST(available_bytes/1024./1024./1024. as decimal(20,2))
as Free_Space_GB,
CAST((CAST(available_bytes as decimal(20,2))*100/total_bytes) as decimal(5,2))
as Percent_Free
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
ORDER BY 1
OUTPUT
