If you are a database administrator (DBA) and have experience with log shipping as a high availability solution in SQL Server, you may have encountered a number of common errors. In this article, we will discuss one of these common errors and how to resolve it.

The error message you may have seen is:

Message 2015-10-13 21:09:05.13 *** Error: The file 'C:\LS_S\LSDemo_20151013153827.trn' is too recent to apply to the secondary database 'LSDemo'.(Microsoft.SqlServer.Management.LogShipping)
*** 2015-10-13 21:09:05.13 *** Error: The log in this backup set begins at LSN 32000000047300001, which is too recent to apply to the database. An earlier log backup that includes LSN 32000000047000001 can be restored. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider)

This error is shown when the history of the restore job fails. If the failure exceeds the configured thresholds, you may also see the following error in the SQL Server ERRORLOG on the secondary server:

2015-10-14 06:22:00.240 spid60       Error: 14421, Severity: 16, State: 1.
2015-10-14 06:22:00.240 spid60       The log shipping secondary database PinalServer.LSDemo has a restore threshold of 45 minutes and is out of sync. No restore was performed for 553 minutes. Restored latency is 4 minutes. Check agent log and log shipping monitor information.

To start troubleshooting this error, you can look at the Job Activity Monitor on the secondary server, which would fail with a specific state. If you have knowledge of SQL transaction log backup basics, you might be able to guess the cause of the error.

In most cases, this error is caused by a mismatch in the Log Sequence Number (LSN). One possible cause is a manual transaction log backup that was taken. There have also been scenarios where a third-party tool took a transaction log backup of a database that was part of a log shipping configuration.

To locate the “out of band” backup, you can use the following query:

-- Assign the database name to a variable below
DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'LSDemo'

-- Query
SELECT TOP (30) s.database_name, m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date, CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType, s.server_name, s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC, backup_finish_date

Running this query will provide you with a list of backups that have occurred on the database. This information is retrieved from the MSDB database.

Once you have identified the “problematic” backup, you need to restore it manually on the secondary database. Make sure to use either the NORECOVERY or STANDBY option so that other logs can be restored. After the file is restored, the restore job will be able to pick up from the same point and catch up automatically.

If you have encountered other problems with log shipping, please share them in the comments. Your input will be of great help to others, and I will try to address those issues in future blog posts.