Error: F_JD0A42: Cannot Find Transaction Log Record for LSN

Issue

A Capture job logs the following error when it cannot locate a required transaction log record for a specific Log Sequence Number (LSN):

F_JD0A42: Cannot find transaction log record for LSN - LSN-based DB transaction log files were unavailable because they were backed up and purged. This issue typically arises when database transaction log files have been purged during a backup.

Environment

HVR 6

Resolution

To resolve this issue, do the following:

Verify transaction log availability

Run the following query to verify that the required transaction log has not been purged:

SELECT file_id, Type_Desc, name, physical_name, state, state_desc, size, max_size, growth 
FROM sys.database_files;

Review backup history

Check whether the backup process has purged the necessary log records. Execute the following query to see the backup history for the affected database:

SELECT database_name, first_lsn, last_lsn, backup_start_date, backup_finish_date,  
    CASE bs.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
    END AS backup_type,  
    physical_device_name, device_type, backup_set_uuid
FROM msdb.dbo.backupmediafamily  bmf
INNER JOIN msdb.dbo.backupset  bs ON bmf.media_set_id = bs.media_set_id  
WHERE  bs.database_name = '<DBName>' 
ORDER BY database_name, backup_finish_date;

Compare the first_lsn and last_lsn values with the LSN in the error message to determine whether the required log segment has been purged.

Enable tracing

Set the following environment variables at the source location to trace SQL queries triggered by HVR:

• HVR_FILE_TRACE=1
• HVR_MSSQL_ZREAD_TRACE=1
• HVR_SQL_TRACE=2

Confirm Archive_log_Path configuration

Ensure the Archive_log_Path points to the correct directory containing archived transaction log files. Confirm that the required log files exist and are accessible.

Verify Archive_log_Format setting

Check that the Archive_log_Format setting matches the naming convention and extention of your archived logs. If unsure, temporarily set this value to * to include all files in the archive folder.

Restore transaction logs from backup

If the log file containing the LSN has been purged, restore it from the most recent backup. After restoring the file, restart the Capture job.

Switch Capture method (optional)

If restoring logs does not solve the issue, consider switching the Capture_Method property value from SQL to DIRECT. When applying this change, ensure the Capture Agent runs on the source database.

Refresh with No Rewind (optional)

As a final step, perform a Refresh operation with the No Rewind option enabled. This ensures HVR reprocesses logs without relying on previous capture states, potentially circumventing the missing LSN.

Cause

This issue may occur for either of the following reasons:

• A backup operation has purged the transaction log record for the specified LSN.
• The restored transaction log file is incompatible with the configured Archive_log_Format.

Recommendations

We recommend regularly verifying transaction log archival and availability. Carefully manage your backup and retention policies to avoid unintentionally removing log files required for replication.