How to troubleshoot Error “F_JD0A42: Cannot find transaction log record for LSN”
Capture job reading from a SQL Server instance fails with the following error message:
2019-07-03T01:10:36-06:00: channel-cap-loc: F_JD0A42: Cannot find transaction log record for LSN [00262487:00092840:0001]. The current TLOG is truncated already, and no matching transaction log backup is found. \
F_JT140F: The previous error occurred before any records were scanned. The scan start time was 2019-07-03T01:04:55-06:00 at address 00262487:00092840:0001 with SEQ 0x26248704942000.
The job is unable to find a specific LSN from 2019-07-03T01:04:55-06:00. Backups are created every 10 minutes. When checking the archive log folder this time period seemed to be covered by the transaction log backups:
/ArchiveLogPath and /ArchiveLogFormat were matching the actual folder structure and file name.
As backup was made at 01:00AM (010000.trn) and one at 01:10AM (011000.trn). The one made at 01:10AM should have contained the transaction HVR is looking for.
Steps to investigate
Check the header of both the 010000.trn and 011000.trn files to see if there is a gap between the LSN values:
RESTORE HEADERONLY FROM DISK = N'C:\path\to\tlog\backup\file.trn'
The LastLSN value of 010000.trn and the FirstLSN value of 011000.trn should be matching. If they are not matching that means the log sequence chain is broken and there are transactions which were never saved to the tlog backups.
If we convert the LSN value mentioned in the error message from hexadecimal to decimal we can see that that the transaction is not included in either of the transaction log backup files. The current LSN is bigger than the LastLSN in 010000.trn and smaller than the FirstLSN in 011000.trn.
LastLSN in 010000.trn: 2499713000074452000001 LSN HVR was looking for: 249971900006001280001 FirstLSN in 011000.trn: 2499722000064300800001
A third-party tool beside the native SQL Server backup is also making backups and calling the sp_repldone function that truncates the transaction log.
Disabling the third-party tool addresses the issue.