1

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?

2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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