I am using ms sql 2008, I wanted to get the total space and available free space on my drives.I used
xp_fixeddrives. Unfortunately I got only free disk Space.So does anyone have any other solution so as to get both total disk space and available disk space in ms sql 2008 or below?
CREATE TABLE #drives ( drive char, [free] int ) INSERT INTO #drives EXEC master..xp_fixeddrives
For ms sql 2008 R2 and above I can get the total drive space using this query:
WITH core AS ( SELECT DISTINCT s.volume_mount_point [Drive], CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs] FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s )