0

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
4
  • are you executing it in the sql server or from a remote ssms?
    – McNets
    Dec 12, 2016 at 11:36
  • I'm executing it on a server it self.I'm using it with SQL job to give status of hard disks.
    – Rehan
    Dec 12, 2016 at 11:39
  • Show this query select * from sys.master_files; databases in your virtual attached HDD?
    – McNets
    Dec 12, 2016 at 11:46
  • nop.none of DB physical path is in other drives all are in C drive only.
    – Rehan
    Dec 12, 2016 at 12:10

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.