topic Re: failed upon SQLExecute with LSN parameters in Qlik Replicate

failed upon SQLExecute with LSN parameters

missDBA — Tue, 15 Dec 2020 07:40:32 GMT

The task for SQLServer to Oracle is running fine until it was aborted wit recoverable error. No errors/alerts in the backend or so and this is the logs for the Replicate. Any thoughts as to what caused this?

00003780: 2020-12-14T19:01:33 [SOURCE_CAPTURE ]E: mssql_execute_log_lsn_peeper(...) failed upon SQLExecute with LSN parameters '0x00013cdf:00092f7a:002c','0x00013cdf:00092f7a:002c' [1020102] (sqlserver_log_queries.c:782)

00003780: 2020-12-14T19:01:33 [SOURCE_CAPTURE ]E: Encountered an unexpeceted error. Aborting... [1020102] (sqlserver_endpoint_capture.c:685)

00011396: 2020-12-14T19:01:33 [TASK_MANAGER ]I: Task error notification received from subtask 0, thread 0, status 1020102 (replicationtask.c:3053)

00003780: 2020-12-14T19:01:33 [SOURCE_CAPTURE ]E: Error executing source loop [1020102] (streamcomponent.c:1905)

00003780: 2020-12-14T19:01:33 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_SRC-SQL-GDSSQLNA102 [1020102] (subtask.c:1368)

00003780: 2020-12-14T19:01:33 [SOURCE_CAPTURE ]E: Stream component 'st_0_SRC-SQL-GDSSQLNA102' terminated [1020102] (subtask.c:1537)

00011396: 2020-12-14T19:01:33 [TASK_MANAGER ]W: Task 'PROD_DS_SQL_ORACLE' encountered a recoverable error (repository.c:5704)

Re: failed upon SQLExecute with LSN parameters

MarinaEmbon — Sun, 20 Dec 2020 11:50:32 GMT

Thank you for this question.

The error you encountered appears when Replicate doesn’t find the LSN on TLOG/backup log.

In some cases, it takes some time for the logs to be presented, that’s why the endpoint returns a recoverable error.

Was the task able to recover and continue the CDC? If so, that's fine and you can ignore this error.

Re: failed upon SQLExecute with LSN parameters

dhina — Mon, 14 Nov 2022 05:21:44 GMT

Hi MarinaEmbon,

We also encountering the same issue with our tasks, but our tasks go into failed state.

Re: failed upon SQLExecute with LSN parameters

Bill_Steinagle — Mon, 21 Nov 2022 20:55:05 GMT

Hi,

Thank you for the post to the QDI Forums. I would check the Change Processing Mode on the SQL Server Source as depending on how this is set will determine if the Task will read from the online TLOG or Backups or both. This would be first step and if support is needed a Support case with the SOURCE_CAPTURE set to TRACE and attach the Diagnostic Package to the case when opened to help expedite the case.

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/SQL%20Server/SQLServerDBSource_AdvProps.htm#Setting%20advanced%20connection%20properties

The SQL Server Source Advanced Tab:

Change processing mode: Choose one of the following change processing modes:
- Prioritize Online Logs - This is the default. Replicate will first look for the changes in the online transaction logs. If the changes cannot be found in the online transaction logs, it will look for them in the backup transaction logs instead.
- Prioritize Backup Logs - When this option is enabled, Replicate will first look for the changes in the backup transaction logs. This can improve performance when reading from the online transaction log is slow (e.g due to lock contention) or when using file-level access to access the backup transaction logs.
  
  If the changes cannot be found in the backup transaction logs, it will look for them in the online transaction logs instead.
- Backup Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the backup transaction logs only. Selecting this method results in increased latency due to the interval between backups. The actual latency time will remain constant, but will vary according to the backup schedule.
- Online Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the online transaction logs only.

Thanks!

Bill

Re: failed upon SQLExecute with LSN parameters

SwathiPulagam — Tue, 29 Nov 2022 11:11:06 GMT

Hi @missDBA,

If your Replicate has access to the backup logs then use the Prioritize Online logs option in Change Processing Mode so that if the LSN is not found in the .ldf file then Replicate tries to read it from the .trn backup file.

Thanks,

Swathi