Often we face the situation where we need to check the total disk space and available disk space for both physical and LUN/Mount drives. The extended stored procedure XP_FixedDrives is unable to help us in this scenario, so we have to log into the machine to check the total and free disk space for each physical/LUN/mount drive. The below script will give the more details like the database name and the files in it which would save our time instead of manually checking the drives for the files containing in it.

SELECT DISTINCT DB_NAME(dovs.database_id) DBName,

mf.physical_name PhysicalFileLocation,

dovs.logical_volume_name AS LogicalName,

dovs.volume_mount_point AS Drive,

CONVERT(INT,dovs.available_bytes/1048576.0/1024.0) AS FreeSpaceInGB,

CONVERT(INT,dovs.total_bytes/1024.0/1024.0/1024.0) AS TotSpaceGB,

CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB,

CONVERT(INT,dovs.total_bytes/1024.0/1024.0) AS TotalSpaceInMB,

Convert(INT,((dovs.available_bytes/1048576.0/1024.0)/(dovs.total_bytes/1024.0/1024.0/10

24.0)) * 100) AS PercentageFree

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY FreeSpaceInGB ASC

By: Madhu Mohan