Change Tracking Automatic & Manual Cleanup

Question

We have a Sql Server Enterprise edition database server on site (version 15.0.43.22). We configured change tracking on a production database. Automatic change tracking cleanup works only if we activate trace flag 8290. Manual cleanup of change tracking fails if we execute exec sp_flush_commit_table_on_demand. The change_tracking_hardened_cleanup_version() return a negative number. This article describes exactly what is happening https://support.microsoft.com/hu-hu/topic/kb4538365-fix-change-tracking-cleanup-does-not-work-when-invalid-cleanup-and-hardened-cleanup-version-are-negative-in-sql-server-2017-and-2019-f186bd92-622c-89f2-a6c2-739c250593f2 . So, there is still a Sql Server bug ?

Answer 1

My reading of the last note in the KB article is this:

• You need to enable TF 8290 for automatic cleanup to work.
• If you still have issues with negative numbers, open a support case.

To resolve this issue, you need to enable trace flag 8290 and let the auto cleanup process run. After this trace flag is enabled, change tracking auto cleanup process will reset the invalid cleanup version to cleanup version based on the retention period. This trace flag can be enabled at session level or at startup level. In order to understand the root cause of why the cleanup version became negative, it is recommended that you call up support and provide more details.

Answer 2

Hi @Raf H

I'd like to suggest you may open a ticket to Microsoft support and the engineers will give the professional advice and help resolve the issue.

Services Hub (microsoft.com)

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.