Change Tracking Automatic & Manual Cleanup

Raf H 0 Reputation points
2023-10-16T12:58:18.3833333+00:00

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 ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 107.2K Reputation points
    2023-10-16T21:09:38.6333333+00:00

    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.


  2. ZoeHui-MSFT 35,556 Reputation points
    2023-10-17T05:36:48.35+00:00

    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.