DBA

Log shipping LSN mismatch issue The log in this backup set begins at LSN , which is too recent to apply to the database

Advertisements

How to troubleshoot LSN mismatch issue in log shipping

I have got an email, how to fix for log shipping, I already have a post for alwayson

https://sqlserverblogforum.com/alwayson/how-to-solve-the-lsn-mismatch-in-sql-server/

For log shipping: you will have entry like this in the job error

Message

2019-12-21 21:09:45.39 ***

Error: The file ‘\LAPTOP-ISGUKEUC\Backup_Copy\T_20191221153913.trn’ is too recent to apply to the secondary database ‘T’.(Microsoft.SqlServer.Management.LogShipping) ***

2019-12-21 21:09:45.39 *** Error:

 

The log in this backup set begins at LSN 31000000048600001, which is too recent to apply to the database.

An earlier log backup that includes LSN 31000000048200001 can be restored. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Following is the code to find the break and fix:

Very easy fix: If we know the date when it got broken, just add that as condition and generate restore script and run it all in once, it will pick up the right file and follow it, get it in asc order.

Run this script on source server and copy the First_LSN of first row.

 

SELECT  'restore database T from disk= ''' +f.physical_device_name+''' with norecovery',
b.backup_finish_date,b.first_lsn,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.server_name ,b.database_name,b.user_name, f.physical_device_name,b.*
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name like'T'
and b.first_lsn<=31000000048200001
order by b.first_lsn  desc

 

Copy paste the copied First_LSN in the condition and b.first_lsn>=31000000048200001

, get the result and restore backup on destination server.

 

SELECT  'restore database T from disk= ''' +f.physical_device_name+''' with norecovery',
b.backup_finish_date,b.first_lsn,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.server_name ,b.database_name,b.user_name, f.physical_device_name,b.*
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name like'T'
and b.first_lsn>=31000000048200001
ORDER BY b.backup_finish_date

If you still could not find or the backup been deleted, then reinitialize the log shipping.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

43 + = 53