Logshipping secondary server is out of sync and LSRestore job failing







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


Problem
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
http://support.microsoft.com/default.aspx?scid=329133



Cause
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.


Resolution
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 :

SELECT
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM
    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
WHERE
    (s.database_name = 'databaseNamePrimaryServer')
ORDER BY
    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