Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Unanswered: SQL 2000 server backup errors

    I am experiencing the following problem when running a backup. This process was working really fine for 3 months.

    BACKUP DATABASE [DBLIVE] TO DISK = N'C:\BACKUPS\dblive' WITH INIT , NOUNLOAD , NAME = N'DBLIVE backup C', NOSKIP , STATS = 10, NOFORMAT

    Database size 10G
    Recovery Mode : Simple
    Daily Backup

    Error message:

    Executed as user: ACC33\Administrator. 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000] (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50 percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up. [SQLSTATE 01000] (Message 3211) Nonrecoverable I/O error occurred on file 'D:\sqldb\DBLIVE_Data.MDF'. [SQLSTATE 42000] (Error 3271) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

    Thank You.
    Last edited by ACCDBA; 08-20-04 at 15:29.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be that your disk is now too full for a complete backup.

    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    7
    Thank You Pat for your reply but the server has 45g of free space, 1g RAM.
    This is what the event viewer has (Application Log).

    18210 :
    BackupIoRequest::WaitForIoCompletion: read failure on backup device 'D:\sqldb\DBLIVE_Data.MDF'. Operating system error 1117(The request could not be performed because of an I/O device error.).
    Last edited by ACCDBA; 08-20-04 at 15:48.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That error message from the event log was caused by something other than the BACKUP statement you posted. Check to find out whether the statement or the error message was posted incorrectly.

    -PatP

  5. #5
    Join Date
    Aug 2004
    Posts
    7
    As you can see the source of the Event log Error was generated from MSSql. I copied the statement from the backup job that had been running fine, up to now. I pasted the statement in the query analyzer and ran the instruction. The backup failed and I get this error in the event log. This is the first of 5 errors generated.

    Thanks for the help
    Attached Thumbnails Attached Thumbnails XX.jpg  
    Last edited by ACCDBA; 08-20-04 at 17:52.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    That error message from the event log was caused by something other than the BACKUP statement you posted. Check to find out whether the statement or the error message was posted incorrectly.
    It is from the backup job, no question.

    ACCDBA, you need to ensure that your database is healthy, because there is an explicit reference to the data device in the error.

    Also, it kind of looks strange that the error that you posted earlier references the data device as a "backup" device.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    It is from the backup job, no question.
    I'll buy that it is from A backup job, but I'm not willing to accept it is from the BACKUP command that was posted. I still believe that the message and the command don't agree.

    -PatP

  8. #8
    Join Date
    Sep 2004
    Posts
    2

    Thumbs up Got a fix

    I got the same problem with one freelance client. I've checked the windows event log and it clearly showed that there was a read/write problem with the harddisk. Error 823. The database was around 6GB, and when I try to back up, it fails at the middle of the operation.

    To fix the problem, I created a new database, use DTS to import data, sp, views and triggers. So it's a complete replica of the corrupted database. Note that the corrupted database still has your data intact. Also, you have to import the tables batch by batch( say 10-20 tables per batch), otherwise DTS fails. In fact, I imported the big tables one by one. Now, I got the database working again.

    Try this commands as well:
    dbcc checkdb - checks the health of a db
    dbcc checktable -checks the health of a table.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    DTS fails probably because your trx log got filled up. Hard disk failure is the most likely cause for ACCDBA's problem. While writing the backup device wich occurs in cluster size increments times pre-estimated factor, a bad sector was encountered and failed the process (unless I'm totally misreading the symptom).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Sep 2004
    Posts
    2

    Cool nah

    Nah, the log db is not full and the harddisk is abundant. There's no limit with the size of the logdb as well.
    But I'll keep that in mind for future encounters.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •