Options
Deadlock Breaks Log Shipping
DonFerguson
Posts: 196 Silver 5
When the full backup and the transaction log backup run at the same time, I sometimes get the following error:
SQL error 3014: SQL error 3014: BACKUP LOG successfully processed 1 pages in 0.019 seconds (0.026 MB/sec).
SQL error 3009: SQL error 3009: Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
SQL error 1205: SQL error 1205: Transaction (Process ID 92) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Processed 1 pages for database 'MYDB', file 'MYDB_Log' on file 1.
The problem is that when this happens, no LSN information is written to the transaction log file. This breaks log shipping on the destination end, because the restore command can't properly sort the SQB file that has no LSN information in it. This results in the "Log files are not in sequence" error on the destination server.
1/12/2009 10:23:10 AM: Warning 170: No LSN information contained in file: 'LOG_MYINSTANCE_MYDB_20090111_223037.sqb'
1/12/2009 10:23:10 AM: Warning 170: Log files are not in sequence: 'LOG_MYINSTANCE_MYDB_20090111_230005.sqb' and 'LOG_MYINSTANCE_MYDB_20090111_232026.sqb'
Please fix this.
SQL error 3014: SQL error 3014: BACKUP LOG successfully processed 1 pages in 0.019 seconds (0.026 MB/sec).
SQL error 3009: SQL error 3009: Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
SQL error 1205: SQL error 1205: Transaction (Process ID 92) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Processed 1 pages for database 'MYDB', file 'MYDB_Log' on file 1.
The problem is that when this happens, no LSN information is written to the transaction log file. This breaks log shipping on the destination end, because the restore command can't properly sort the SQB file that has no LSN information in it. This results in the "Log files are not in sequence" error on the destination server.
1/12/2009 10:23:10 AM: Warning 170: No LSN information contained in file: 'LOG_MYINSTANCE_MYDB_20090111_223037.sqb'
1/12/2009 10:23:10 AM: Warning 170: Log files are not in sequence: 'LOG_MYINSTANCE_MYDB_20090111_230005.sqb' and 'LOG_MYINSTANCE_MYDB_20090111_232026.sqb'
Please fix this.
Comments
Try skipping the deletion process for a while, and see if the deadlock issue still occurs. If all is fine, I would then suggest setting up additional SQL Server Agent job(s) to perform the deletion using the msdb..sp_delete_backuphistory or msdb..sp_delete_database_backuphistory (SQL2008) stored procedures, outside of your backup/restore periods. Note that if you are using SQL 2000, you may want to make some adjustments, as the standard procedure isn't efficient (google for more information).
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Actually no. But it turns out the a regularly scheduled execution of the sp_delete_backuphistory procedure was occurring at the same time the deadlocks were. So I have shuffled the schedule of that so it doesn't run at the same time the transaction logs run. I am using SQL 2005 and this procedure takes about 3 minutes to complete. It is set to delete everything older than 30 days and runs nightly. I may be able to create a custom version of this procedure (using a new name of course) that doesn't hold locks while it deletes the records.
This may be a viable work around on my end, but the fact that the LSN information doesn't get recorded in the backup file when this happens, should still be fixed on your end. Another option might be to sort on file name instead of LSN information when using a wild card restore.
USE [msdb]
CREATE NONCLUSTERED INDEX [idx_physical_device_name] ON [dbo].[backupmediafamily]
(
[physical_device_name] ASC
)
CREATE NONCLUSTERED INDEX [ix_media_set_id] ON [dbo].[backupset]
(
[media_set_id] ASC
This will hopefully resolve or at least mitigate the deadlock issue.