Qlik Replicate: SQL Server Backup failed while Task was up and caused invalid LSN

[David_Fergen]

When a SQL server is running as the Source and have a Backup of the Database. The task was running against a fail while the task was running and being recorded by the LSN from the backup that failed. There was a backup of the SQL Server Database that was being checked. As they are using Backups only and there was a new LSN once the other backup completed which was different from the one recorded.

Other Situations the issue could happen:

1. This can also happen if the backup device they are using is not supported.

1. This can happen if they are running online only T log transaction enabled. Also, if Agent reader is active when using sp_repldone in the task using online only they need to disable the Log Reader Agent.

1. When looking at logs there should be a current and last LSN, and if they do not match there will be an error as well.

Info bout sp_repldone:
Using sp_repldone truncates the SQL Server TLOG and instead of the Agent Reader we have control to truncate the log which holds the open transactions for the tables defined in the task.

Resolution:

A Full Load of the Parent Task and was done and uses a new LSN from the new backup. At that point the task has started working again. The child task that they had started had a timestamp that was after the Parent Task started with the new LSN to start picking up the changes.

Instructions: If the Task failed, make sure to check in SQL Server with the last backup and query the current LSN from the backup that completed.
​
 Use this Query to get last LSN to compare to invalid LSN:

SELECT
last_log_backup_lsn
FROMsys.database_recovery_status
WHEREdatabase_id=DB_ID()

Example of Query showing the backup device and type:

 select  bmf.physical_device_name,    
bs.position,    [dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn),    
[dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn),    
bs.backup_set_id  from  msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset
bs  where bmf.media_set_id = bs.media_set_id  and bs.backup_set_id > 0  and bs.database_name=db_name() and bs.type='L'

Note: There could be another result shown with Invalid LSN and it will not show the query. It shows the first and last LSN that do not match.

​Note: Notice that the first and last LSN that was looked up was not found.

Takeaway: In future cases that the backup failed use these steps listed above to help quickly find the issue.

Note: The solution can vary depending on the use case and on the error, they are getting. If the backup device not being supported, we can let them know and they can switch to online only using sp_repldone.