LSN mismatch error in Log Shipping | SansSQL

Wednesday, May 1, 2013

LSN mismatch error in Log Shipping

LSN mismatch is a common issue which happens in Log Shipping and because of which the Log Shipping goes out of sync.
When you check the history, you will find messages like below
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 36000000048400001, which is too recent to apply to the database. An earlier log backup that includes LSN 20000000022100001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
According to this error message, I am trying to restore a log backup before which I need to restore another backup which is taken earlier than this.

Few things to note,
  1. When you have setup log shipping, avoid adhoc log backups
  2. If you are really required to take log backup, then take it using the log shipping backup jobs 
  3. Or use COPY_ONLY option
Ok, now coming back to the error message, we have to find out the name of the backup file and its location relating to the LSN number mentioned in the error so that we can restore this first and which will then allow other logs to be applied.
To find the name of the backup file and its location relating to the LSN number mentioned in the error, use the below query.

DECLARE @first_lsn nVarchar(100)
SET @first_lsn = '20000000022100001' --Change the LSN Number

SELECT database_name AS DatabaseName
   ,physical_device_name AS BackupLocation
   ,backup_start_date AS BackupStartDate
   ,backup_finish_date AS BackupEndDate
   ,CAST(backup_size/1024/1024 AS DECIMAL(10,3)) AS BackupSizeInMB
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id=BMF.media_set_id
WHERE BS.first_lsn = @first_lsn

Once you get the filename, check if the backup is available and apply it.
Then restart the Log Shipping restore job to continue restoring the remaining log backups


Atchi said...

If there is no Respective of the log file backup.then we have to take full backup and needs be to restore it into secondary Server.

Is this Correct Process?

Johnson Welch said...

This was really very helpful, Fantastic blog. There I found one more blog considering log server for backup. Have a look:

Post a Comment