Jun 22, 2022 3:34:02 AM
Jun 22, 2022 3:34:02 AM
This article aims to explain how SQL server T-log cleans up works when Microsoft replication\publication is enabled on the database.
Environment
When the Qlik Replicate task first time runs to capture CDC, Qlik Replicate will create a publication on the database with required articles. As part of this publication log reader agent job also will be created and this job will continuously run to mark replicated transactions on the database.
Apart from the Replicate process, there will be a transactional log backup job that will run every 15 mins or 30 mins depends on source team policy. As part of this log backup job, all the transactions will be backup up to that point in time and truncate all replicated and committed transactions from T-log.
Assume, there is a scheduled t-log backup job going to run at 10 am, and the replicate task is reading transaction log with 5 mins latency, there is a high possibility that the backup job will remove the transaction(s) from a transactional log which hasn't read by the Qlik Replicate. In this scenario, the Qlik Replicate task will be failed with a missing LSN error.
To prevent such kinds of issues Qlik Replicate implemented an option to hold T-log for a couple of mins without truncating based on the below setting:
Qlik Replicate creates an internal table called attrep_truncation_safeguard on the source database and always runs two update queries (2 update queries for each Qlik Replicate task running on the database) without commit (called Latch Lock A and B), only when you enable Start transactions in the database setting on source SQL endpoint. Qlik Replicate will update the time on these queries every 5 mins by default and we can control time by using an Option called "Apply TLOG truncation prevention policy every (seconds): ".
Here are the screenshots to explain how to check these open transactions on the database.
- You can find the setting below in the source endpoint which controls truncate policy:
- We can see the uncommitted transactions from Qlik Replicate UI.
- Verify Open transactions on the database. You can check the task name in the open Tran.
- We can check the attrep_truncation_safeguard table for latch lock information.
- Here is how we can find corresponding SQL sessions for each latch key.
- Finally, we can see the exact update queries without commit(open transactions) running on the database.
