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.

This issue has been puzzling me for some time now and I'm running out of ideas! Any help would be much appreciated! Here's the problem:

MS SQL Server 2008 running on Windows Server 2008R2 (Virtualized) Database backups are failing for all (larger) Databases on a particular server. Error code 27 suggests "The drive cannot find the sector requested.".

There is plenty of available space on backup drive, and I have tried backing up to other drives on the server which also fail.

I am able to backup the system databases without any trouble. (presumably due to their small size)

I created a new blank database and was able to back this up.

Manual backups of the main Databases fail (using 'right click' or TSQL). They get to 30% and the fail as if they have run out of space.

This is a virtual server running on a two node cluster. I've tried failing the VM to the other node, but this doesn't help.

Tried splitting the backup into five parts, but the job starts hanging again at 30%.

I tried manually copying one of the old backups to the drive using Windows Explorer. The file copied successfully.

It feels like a problem with the drives, however it's only an issue from within SQL Server.

share|improve this question
    
What kind of errors stand out in the SQL Server error log? The Application Event Log? System Event Log? –  Thomas Stringer Oct 24 '13 at 12:44
    
Application Log has SQL errors stating: BackupMedium::ReportIoError: write failure on backup device 'h:\Mssql\Backups\'. Operating system error 27(error not found). –  Sam Oct 24 '13 at 13:01
    
What account is the SQL Server service running as? Is `H:` a mapped drive? If so, your best bet is probably to use a UNC path instead of the mapped drive, because usually the service account can't see the same mapped drive you do (they're account-specific). –  Aaron Bertrand Oct 24 '13 at 13:03
add comment

1 Answer 1

up vote 1 down vote accepted

Standard options for me to check would be the following, but this has been done by you or does not match exactly because the backup at least starts:

  • Disk space sufficient? (If possible do a chkdsk)
  • Write Access granted to SQL Server?

Next step would be to check throughput of backup drive because SQL Server backup is sensible in this area, some timers in the background are watching. Backup to a USB-Drive / Stick is usually a very bad idea because of this.

Now for the more exotic stuff:

  • H:-Drive is NTFS formatted? Otherwise File size >4 GB might be an issue.
  • As we are dealing with a VM here: Is there some kind of disc overcommitment happening on Hyper-V or SAN level? Just because you see a e.g. 1 TB drive does not guarantee that this is covered by physical disks somewhere.
  • Any I/O errors in windows system event log? If something really bad is happing on I/O level you can find it there.
share|improve this answer
    
Thanks for the comments. Disk space isn't an issue, even at SAN level. I was able to manually copy an 80Gb file to the drive as a test. DB backups are only about 60GB. Drive is indeed NTFS formatted. Can't see any obvious I/O errors in the event log. Have tried a chkdsk on the drive in question. I'm going to try adding an additional drive from a different lung. Will let you know if it works. –  Sam Oct 24 '13 at 14:10
    
UPDATE: New drive worked. It looks like you were right, I was seeing 850Gb with 350GB free, however the drive only had 650GB reserved on the cluster. It seems this went wrong after a maintenance weekend when some storage was moved about. The strange thing is that, technically I should still have had 150Gb free. Think we're sorted now though! Thanks for your help. –  Sam Oct 24 '13 at 15:18
1  
@Sam is your backup using compression? If so, it will create the full uncompressed size first, then shrink. So, you will usually need a lot more space initially than the file you end up with –  Aaron Bertrand Oct 24 '13 at 18:12
    
@Aaron You're right, that'll be it! :) –  Sam Oct 25 '13 at 9:16
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.