Modify SQL Query Text How can I make below query work for virtual attached HDD. As of now this query return only C drive even though I have other drives also.

SELECT DISTINCT dovs.volume_mount_point AS Drive,
CONVERT(decimal,dovs.available_bytes/1048576/1024) AS FreeSpaceInGB,
convert(decimal,dovs.total_bytes/1048576/1024) as TotalSpaceInGB,
cast(CONVERT(decimal,dovs.available_bytes/1048576/1024)/convert(decimal,dovs.total_bytes/1048576/1024)*100 as decimal(38,2)) as FreeSpaceInPct
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInGB ASC
sys.master_files is a DMV for databases, so it is by design that it does not show drives that don't have databases.

If you need this functionality, consider writing an xp_cmdshell script inside your job to get this information. You'll need to know Powershell.