Determine free space on “nonfixed” drive

Question

I am writing a stored procedure that needs to send an email when a dvd rw drive is almost full. I found master.sys.xp_fixeddrives but it only shows free disk space for fixed hard drives. I have not been able to find something that will tell me the free space for "nonfixed" drives. Is there a undocumented sp or function that I don't know about?

Answer 1

Alternative could be to use PowerShell to grab the information. All you need is a few commands tied together:

$measure = Get-WmiObject Win32_LogicalDisk | Where {$_.DriveType -eq 5}
if ($measure.FreeSpace -lt 20 { "More logic"} else {"something else"}

You could add in the logic to measure for your limit (value is in bytes). Then use Send-MailMessage cmdlet to actually send out your message. Wrap all that up and then have a SQL Agent job call the script on a scheduled basis.

Answer 2

I was not able to test with a DVD drive, but you can try using the xp_cmdshell procedure. If your DVD is on the D drive, then try xp_cmdshell 'dir D:'. The last row (before NULL) will give the free space. A bit of parsing will be required.