Log shipping problem: log not copied after error 920
tombriggs
Posts: 12
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
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
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.
Redgate Foundry
Just to let you know this has been addressed for a future release.
Thanks,
Redgate Foundry