Perfomance overhead on qlik replicate

[ugoaku]

Dear Team:

I have a situation where an update to the table attrep_truncation_safeguard created on source SQL server database ran for 28 days. It was observed that this action created an impact on the tempDB of the SQL server database by filling the disk space which in turn slowed down the database. I would like to understand if an update to the attrep_truncation_safeguard creates overhead performance and how this can be mitigated?

Thank you

[Heinvandenheuvel]

If you had searched this forum for "attrep_truncation_safeguard" then you would have found a topic which is of relevance. Read the suggested solution carefully and see if this helps: https://community.qlik.com/t5/Qlik-Replicate/What-is-Latching-in-Qlik-replicate/m-p/1889962#M1815

Hein.

[Kent_Feng]

Hi @ugoaku 

attrep_truncation_safeguard is a table created in SQL Server by Replicate that records the history of how replicate tasks (see column latchtaskname) lock the T-log (see column latchLocker for the locking time). You can stop all related tasks (those use SQL Server as source endpoint), then truncate the table to free some space.

Kent

[Heinvandenheuvel]

@Kent_Feng "truncate the table to free some space." 

I don't believe that to be good advice. Please carefully read Lyka's note.

The space occupied by attrep_truncation_safeguard is trivial, irrelevant: two rows/task.

The dynamic interactions are what matter as it always has an open transaction (A, or B).

"Replicate that records the history of how replicate tasks ...  lock the T-log"

Sort of. But no. There are only two rows (A and B) and with that only two dates for the most recent transaction start. So recent history yes, long term no. And it does not lock the T-log. It just make sure it is not truncate at inopportune moments.

Hein.

[ugoaku]

Thanks @Kent_Feng for the feedback. However I observed something weird as the related task which was tracked by the attrep_truncation_safeguard was paused or stopped a while back, but reviewing the syshistory in SQL server db shows that this task remained in the tempdb for those length of days. I was expecting that having being paused the task such transactions should have been rolled back and not stored in the tempdb for that lenghty period of time.

***Hope you stay safe from Down Under 🤓 ***

[ugoaku]

Thanks @Heinvandenheuvel not sure why i did not come across this. Probably my search criteria did not get it. My main concern is what i asked to @Kent_Feng . Probably you could shed some light.

Thanks

[Heinvandenheuvel · Accepted solution]

If the tasks is stopped (paused ready to resume) then indeed all connections should be gone and any non-commited transaction rolled back. That would be MS SQL's responsibility to takd are of cleanup if/as needed. I suppose you could try to access/update A & B entries for  the suspect task to be sure nothing is 'stuck' on that level. This all 'feels' like an MS SQL issue albeit possibly triggered at some point by Replicate (mis)behaviour.

[DesmondWOO]

Hi @ugoaku ,

As @Heinvandenheuvel mentioned, Replicate prevents TLOG truncation by opening two transactions on the attrep_truncation_safeguard. These transactions involve update operation on the same records and so attrep_truncation_safeguard table should not consume significant disk space. Once Replicate task has been stopped, all connections should be gone and these transactions are rolled back. 

By default, tempdb is set to autogrow. It should be re-created when you restarts SQL server. If you prefer not to restart SQL server, you can try its built-in functions to shrink the database. 

Regards,
Desmond

[Kent_Feng]

Hi @ugoaku 

Thanks for the feedback. and thanks @Heinvandenheuvel  for your comments. I did some test in my lab and confirm that attrep_truncation_safeguard table should not consume a lot of disk space just like @DesmondWOO advised. Each task only takes two rows and these two rows will be updated when CDC continued to run.

My test also indicated that you can delete some rows or even truncate the table when related tasks are stopped and all connections are gone. However, since the table does not take up a lot of disk space, this is not necessary. When your task is running, you should not do any maintenance against this table, just like Lyka's article advised.