Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks Shawn this works great. –  mpenrow Jul 19 '11 at 13:11
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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