QLIK Task still Running but stop to Apply Changes for all tables

[Leandro_Medeiros]

Hi everyone,

After 4 days with the task running very well it pause the sync with Azure, caching a lot of transactions on disk ( zero Applied Throughput and high Apply Latency ).

It has paused the sync at 7:52 but the Task still up ( in running state ). Below printscreen was took near 9:45

Error identified on QLIK Task at 07:51:

Checking the Azure SQL side we´ve detected high CPU usage and found one blocked session.

After identified the blocked DDL, we stopped that Task, killed the blocked SQL session_id and started the Task in Resume mode to try to recover all uncommited transactions, but QLIK paused again with the same or similar DDL.

Probably by corrupted date in this  control table [TARGET_APPLY    ]E:  Failed to get table xxxxxx.attrep_changes01BDB868E4E385DA [1022502].

As the last option/action we stopped SPACE Task again and did a new Full Load.

The SQL script to found the blocked session is:

SELECT

    req.session_id,

    req.status,

    req.start_time,

    req.cpu_time AS 'cpu_time_ms',

    req.logical_reads,

    req.dop,

    s.login_name,

    s.host_name,

    s.program_name,

    object_name(st.objectid, st.dbid) AS 'ObjectName',

    REPLACE(REPLACE(SUBSTRING(st.text, (req.statement_start_offset/2) + 1,

        ((CASE req.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)

        ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1),

        CHAR(10), ' '), CHAR(13), ' ') AS statement_text,

    qp.query_plan,

    qsx.query_plan AS query_plan_with_in_flight_statistics

FROM sys.dm_exec_requests AS req

JOIN sys.dm_exec_sessions AS s ON req.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st

OUTER APPLY sys.dm_exec_query_plan(req.plan_handle) AS qp

OUTER APPLY sys.dm_exec_query_statistics_xml(req.session_id) AS qsx

ORDER BY req.cpu_time DESC;

Please, do you have any suggestion or idea about what is happing?

Any help will be very appreciated.

Thank & Regards

Leandro Medeiros

[DesmondWOO · Accepted solution]

Hi @Leandro_Medeiros ,

In 'Batch Optimized Apply' mode, Qlik Replicate creates a temporary table named attrep_changes and performs a preprocessing action to group transactions into batches efficiently. According to the error message:

[TARGET_APPLY] E: Failed to get table xxxxxx.attrep_changes01BDB868E4E385DA

It seems that Qlik Replicate is unable to locate the net change table. Please set the TRACE/VERBOSE level on TARGET_APPLY and check the task log to determine why Replicate failed to find the table.

If you are still unable to identify the root cause, please create a support ticket.

Regards,
Desmond