What are the challenges you face when working across database platforms? Take the survey
Options

Log shipping problem: log not copied after error 920

tombriggstombriggs Posts: 12
edited February 27, 2009 2:28AM in SQL Backup Previous Versions
We're using SQL Backup to generate txn log backups for log shipping, generally with good results.

A few times lately I've had a transaction log backup fail with the following error:

2/19/2009 1:00:12 PM: Error 920: Failed to update backup history table (BACKUPMEDIAFAMILY).
2/19/2009 1:00:12 PM: SQL error 1205: Transaction (Process ID 183) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now, I understand why this is happening, and I'm not overly concerned about it (though maybe I should be... :P)

What frustrates me, however, is that because of this error, the transaction log backup doesn't get copied to the network share. As a result, the standby server never sees the log backup in question, and thus when it tries to apply the next log backup, it fails because the LSN is too recent. Copying the log backup to the standby system manually and restoring it solves the problem, but... something doesn't seem right about that.

So what's the best thing to do here? Is there a way to force the log to be copied to the network even if the backupmediafamily table can't be updated? Is there anything I can do to reduce the contention errors on the backupmediafamily table, which should at least make this less likely to happen? (The backup history is not auto-deleted, so the suggestion seen in a different post to reschedule that won't help.) Any other options?

Thanks
-Tom

Comments

  • Options
    Hi Tom,

    Unfortunately there is no way to force the copy of the log file in the result of an error. Any error code over 500 represents an error and thus post backup tasks are not executed.

    Do you have a lot of backup jobs which all run around the same time?

    If you have a separate job for each database and they are scheduled to start at the same time this error will be more likely to occur.

    Thank you for your feedback regarding an option to force the copy in the event of this error.
    Matthew Flatt
    Redgate Foundry
  • Options
    There are a number of backup tasks that start at the same time and run very quickly, yes. The contention isn't surprising; reacting to the error in a way that knowingly breaks log shipping is though.
  • Options
    Hi Tom,

    Just to let you know this has been addressed for a future release.

    Thanks,
    Matthew Flatt
    Redgate Foundry
Sign In or Register to comment.