Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Aug 28, 2020 5:44:51 AM
Jul 21, 2020 4:32:49 PM
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:
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.