Logshipping secondary server is out of sync and LSRestore job failing

Logshipping secondary server is out of sync and transaction log restore job failing.

You can see that your logshipping is broken. In the SQL Error log,  the message below is displayed :

Error: 14421, Severity: 16, State: 1.
The log shipping secondary database myDB.logshippingPrimary has restore threshold of 45 minutes and is out of sync. No restore was performed for 6258 minutes.

Description of error message 14420 and error message 14421 that occur when you use log shipping in SQL Server

Inside the LSRestore job history, you can find out two kind of messages  :

- Restore job skipping the logs on secondary server

Skipped log backup file. Secondary DB: 'logshippingSecondary', File: '\\myDB\logshipping\logshippingPrimary_20090808173803.trn'

- Backup log older is missing

*** Error 4305: The file '\\myDB\logshipping\logshippingPrimary_20090808174201.trn' is too recent to apply to the secondary database 'logshippingSecondary'.
**** Error : The log in this backup set begins at LSN 18000000005000001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000004900001 can be restored.

Transaction Log backups can only be restored if they are in a sequence. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for transaction log backups to be out of sequence. Some of the most common reasons are a redundant transaction log backup jobs on the primary server that are causing the sequence to be broken or the recovery model of the database was probably toggled between transaction log backups.

At this time, to check if there are a gaps in the Restore Process. You can run the query below to try to find out whether a redundant Backup Log was performed :

    msdb..backupset AS s INNER JOIN
msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
    (s.database_name = 'databaseNamePrimaryServer')
    s.backup_finish_date DESC;

Microsoft SQL server logshipping

You can see that another Backup Log was running out of logshipping process. Now, you have just to restore this backup on the secondary and run the LSRestore  Job.

Understanding Logging and Recovery in SQL Server

Michel Degremont | Microsoft EMEA
Product Support Services Developer - SQL Server Core Engineer |

Skip to main content