Deadlock Breaks Log Shipping

DonFergusonDonFerguson Posts: 202 Silver 5
edited January 16, 2009 5:37PM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Is SQL Backup set up to delete old entries in the backup and restore history tables in the msdb database? If this is set, the deletion process runs after every process, which may contribute to the deadlock issue.

    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • DonFergusonDonFerguson Posts: 202 Silver 5
    Is SQL Backup set up to delete old entries in the backup and restore history tables in the msdb database? If this is set, the deletion process runs after every process, which may contribute to the deadlock issue.

    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.
    · Share on Twitter
  • DonFergusonDonFerguson Posts: 202 Silver 5
    Rather than creating a custom sp_delete_backuphistory procedure, I found that simply adding the following indexes tremendously speeds up the backup history cleanup process. It also makes the SQLBackup code that retrieves the LSN information much more efficient.

    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.
    · Share on Twitter
Sign In or Register to comment.