Qlik Replicate causing 2 Different Tasks on same Database to block

[Nitesh_22]

Hi,

I have a SQL Server Target Database which contains over 50 tables. We are seeing that Qlik is blocking itself, usually one of the tasks fail

Scenario
Qlik Task 1 : is a Full + CDC task which replicates data to table A
Qlik Task 2 : is a Full Load only task scheduled a few times a day using Qlik Replicate's scheduler which Truncates the table and performs a full load each time

What we have seen in that the TRUNCATE task (Task 2 : Full Load only task) when it kicks off by the scheduler gets blocked by the BULK INSERT of Task 1. Why is this happening as Task 1 and Task 2 is replicating data to 2 different tables even though they are in the same database

Screenshot Attached shows SPID 544 - Task 2 (TRUNCATE Full Load Only being blocked by SPID 546 of Task 1 (Full Load + CDC). As mentioned they are replicating to two different tables in the same database

Regards

Neetz22

[narendersarva]

Hi @Nitesh_22 
Even though Task 1 and Task 2 are targeting different tables, the issue could be due to SQL Server locking behavior. Try to use lock monitor query to observe real time blocking

Thanks
Naren

[john_wang]

Hello Neetz22, @Nitesh_22 

I'm not certain about the exact cause of the locking conflict. To investigate further, please open a support ticket and include the verbose task log files. Our support team will be happy to analyze the behavior and provide confirmation.

As an alternative workaround, you can add the following internal parameter to the target endpoint:

$info.query_syntax.truncate_table

set its value to DELETE FROM ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC}

Please let me know if this approach works for you.

Note: Be aware that replacing TRUNCATE TABLE with DELETE can have significant impacts:

• DELETE operations typically take much longer than TRUNCATE TABLE.
• DELETE is always fully logged in the SQL Server transaction log (TLOG).
• Unlike Oracle, SQL Server does not support a NOLOGGING option for DELETE.

Let me know if you have any questions or need assistance with the configuration.

Regards,

John.