locked
SQL Server Maintenance Plan - Backup Database Failing RRS feed

  • Question

  • I am running SQL Server 2000 Standard Edition (Product Version 8.00.2039 SP4) on Windows 5.0 (2195).  I have a maintenance plan that backs up the the database every night at 10:00 PM.  It has failed several times in a row with the following messages written to the log:

    First Error Message:

    Source: spid 88

    Message:

    BackupMedium::ReportIoError: write failure on backup device 'H:\MSSQL\Backup\RSRS\RSRS_db_200811242200.BAK'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).

     

    Second Error Message:

    Source: spid 88

    Message:

    Internal I/O request 0x27B35C88: Op: Write, pBuffer: 0x14B50000, Size: 983040, Position: 23918614016, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x91A91600, OffsetHigh: 0x5, m_buf: 0x14B50000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile: H:\MSSQL\Backup\RSRS\RSRS_db_200811242200.BAK

     

    Third Error Message:

    Source: backup

    Message:

    BACKUP failed to complete the command BACKUP DATABASE [RSRS] TO  DISK = N'H:\MSSQL\Backup\RSRS\RSRS_db_200811242200.BAK' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT

     

    This has happened before, and I manually backed up the database not using the maintenance plan task.  This was successful.  The maintenance plan than worked for 4 days and then started failing.

     

    I created a separate backup job, and ran that.  It failed after getting to 90 percent with the following error:

    Executed as user: sa. 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)  70 percent backed up. [SQLSTATE 01000] (Message 3211)  80 percent backed up. [SQLSTATE 01000] (Message 3211)  90 percent backed up. [SQLSTATE 01000] (Message 3211)  Write on 'H:\MSSQL\Backup\RSRS\RSRS_db_200811252200.BAK' failed, status = 33. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3202)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

     

    I am in the process of attempting another db backup, and only backup up the database, not using any other settings.

     

    Each backup file is 24,307,889 KB in size.  The job is set to keep 3 files.  There is 428 GB free space on the hard drive.  Also, manually deleting 1 or 2 of the older backup files doesn't help.

     

    Any help or suggestions would be appreciated.

    Thanks

    Tuesday, November 25, 2008 6:18 PM

Answers

  • From what I've seen, this error tends to happen a lot when using compressed folders.  And technically, it's not supported.  See:  http://support.microsoft.com/Default.aspx?id=231347.

    Also, there's some recommendations for workarounds here:  http://www.sqlservercentral.com/Forums/Topic157874-24-1.aspx

    I wouldn't think it would be an issue of append or overwrite since, as you mention, the maintenance plan by default will create a new file for each backup.
    Wednesday, November 26, 2008 2:58 PM

All replies

  • I guess someother process is holding a lock on the backup file as a result you are unable to append the existing backup file.

    H:\MSSQL\Backup\RSRS\RSRS_db_200811242200.BAK'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).


    May be some tape backup might be using the backup file. Try to backup the database to a seperate file .

    - Deepak
    Tuesday, November 25, 2008 6:29 PM
  • Is there a file system backup running at the same time?  Are you backing up to a compressed volume?
    Tuesday, November 25, 2008 6:35 PM
  • The properties for the backup directory are set to Compress drive to save disk space, and allow indexing service t index this disk for fast file searching.

    I am trying to find out what time the file system backup is running.  Although, I wouldn't think this would be a problem since I was able to get the backup to run successfully for 4 straight days.

    I was thinking it might have something to do with the option to append to media, or overwrite media.  When a backup is created through a maintenance plan, you don't have the option to specify one vs. the other.

     

    Tuesday, November 25, 2008 7:17 PM
  • From what I've seen, this error tends to happen a lot when using compressed folders.  And technically, it's not supported.  See:  http://support.microsoft.com/Default.aspx?id=231347.

    Also, there's some recommendations for workarounds here:  http://www.sqlservercentral.com/Forums/Topic157874-24-1.aspx

    I wouldn't think it would be an issue of append or overwrite since, as you mention, the maintenance plan by default will create a new file for each backup.
    Wednesday, November 26, 2008 2:58 PM
  • I turned off compression on the folder where the back-ups are running.  I was able to get a back up to run successfully after that.  I'll let it go for a few days and see if I get any more failures.

    If I can get 4-5 days without a failure, then I think the problem is resolved.

    I'll post another reply middle of next week if all goes well.

    Thanks for your help.

     

    Wednesday, November 26, 2008 9:04 PM
  • Turning off compression seems to have allowed the backup to complete successfully.  However, the job still shows failure, but there is no error message, either in the job history, SQL Server Log, or in the Window's event viewer.  The backup file was created, but the job was not deleting the older files for some reason.

    I have tried recreating the job to see if that fixes that issue.  Will have that answer tomorrow.

    Tuesday, December 2, 2008 4:32 PM
  • What about in the Maintenance Plan history?
    Tuesday, December 2, 2008 7:59 PM
  • Looks like it couldn't delete one of the backup files.  Not sure why that would happen.  Its running under the same account as the other backup jobs, that successfully delete files.  I deleted it manually.  Will have to double check in the morning to see if it was successful now.

    Thanks,

    M

    Tuesday, December 2, 2008 8:15 PM
  • Job finally ran successfully and deleted the older backup file.  Thanks for the help.

     

    Thursday, December 4, 2008 4:11 PM