Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

SQL Server 2005 Backup Failure - I/O Related?

Hi,

Yesterday our SQL Database started to consume 100% processing power on a windows server 2003.  After a restart of the sql service the database went into recovery mode but failed at the last hurdle.  I restored the database to a full backup taken a couple of days ago, they are due to run every morning, but the past couple had failed.  

After the restore, this mornings scheduled backup also failed to run.  The backup is set to write to the same physical disk.

I have since manually tried to do a full database backup, but this has also failed.

Immediate error Message in SQLSM Studio was: "Write on "D:\[FILENAME].bak" failed: 27(error not found)"

Looking at the Application Log Error Message:

"BackupMedium::ReportIoError: write failure on backup device 'D:\[FILENAME].bak'. Operating system error 27(error not found)."

What also/may be related to this problem is a warning in the System log.  It originates from clients uploading zip files to the same physical drive (all be it at different times through the night) and the error is:

"Event Type:      Warning
Event Source:      Ntfs
Event Category:      None
Event ID:      50
Date:            22/04/2009
Time:            04:55:12
User:            N/A
Computer:      DSVR006169
Description:
{Delayed Write Failed} Windows was unable to save all the data for the file . The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere"

AND...

"Event Type:      Information
Event Source:      Application Popup
Event Category:      None
Event ID:      26
Date:            22/04/2009
Time:            04:55:12
User:            N/A
Computer:      DSVR006169
Description:
Application popup: Windows - Delayed Write Failed : Windows was unable to save all the data for the file D:\[FILENAME].zip. The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere. "

After doing some searching via google I was half expecting to see corresponding error message as above for the sql BAK file, but there are no such messages - so i may be barking up the wrong tree here...

I'm really getting a bit stuck on this now and running out of ideas.  I have a few exploratory options that I am about to do tonight (as this is a production server) such as  "DBCC CHECKDB
" and "CHKNTFS", but other than that I am running out of ideas!  And this is very urgent as at presnet, out last full backup was 4 days ago!  Is there any analysis on the database I can do during high usuage hours?

The database is set to FULL recovery mode, I am however about to change this to simple, as we only take nightly backups, with no log backups!!!  Am i right in thinking after to changing to simple and taking a full (successful) backup I can then shrink the log (.ldf) file?  I think the problem has originated from an ever growing log (.ldf) file.  Currently at 120GB with a DB file size around 22GB

This is a failry large, not well maintaned/setup database that I have inherinted. Sigh....

Thanks in advance!
0
K10uk
Asked:
K10uk
  • 2
2 Solutions
 
pcelbaCommented:
{Delayed Write Failed} If OS cannot write its buffers to the disk it really seems to be a hardware problem. Something wrong is either on your disk or its cables or elsewhere. This bug is hard to explore.

We have been solving similar problem here (http://www.experts-exchange.com/Microsoft/Applications/FoxPro/Q_24310626.html?cid=236).

You may start to minitor what consumes so many resources and in which situations the delay write is not successful but the OS reinstallation seems to be the easiest step to avoid some corrupted drivers.
0
 
Chris MConsulting - Technology ServicesCommented:
Hi, you are definitely experiencing OS errors.
From the error below, you could be experiencing some disk problems, the write process does not get to complete writing consistently to the disk.
Application popup: Windows - Delayed Write Failed : Windows was unable to save all the data for the file D:\[FILENAME].zip. The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere. "
 
So I have a question: do you have some other disk that you can backup to other than drive D?
I suggest that you check drive D for sector failures. if you're working off a drive which resides on a centralised storage system then get to fix your connectivity errors with your storage experts.
For a start, try backing up to a different drive other than D drive and after a successful backup then you may have to shut down SQL server and any applications which might be accessing D drive on your system, then run a CHKDSK on your D drive.
If you have any disk errors then get them fixed. This will solve your file writing issues.
You will have to check how consistent your database is using DBCC CHECKDB before backing up to ensure that you backup consistent data.
Regards,
Chris.
0
 
K10ukAuthor Commented:
Thanks for both your posts.

As you've pointed out there seems to be some sort of disk write problem.  However, is this definately connected to the SQL Backup not succeeding?   I only ask as my priority at the minute is to get a Backup succeeding ASAP.

Unfortunately there is no other disk that I can back up to, maybe a network drive to another server within the private network (our webserver)  but I would presume that to be very slow/problematic?

I suppose I'm wondering what is causing the Backup to fail - is it a disk write problem with the OS, a Database integrity problem, both of these, or something else?  The error log from the database backup does not give me much to go on, I am only using the other error messages as a possible relation as both error messages mention writing to disk...?


0
 
pcelbaCommented:
Backup causes intensive disk operations so the error will appear with much higher probability than in normal operations.

Connect external (USB) disk to your server and create backup on it.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now