Symptoms
Assume that you enable the Change Tracking for one or more tables in Microsoft SQL Server 2017 or 2019, and you have set AUTO_CLEANUP = ON. You notice that no rows are deleted or cleaned up from side tables or the sys.syscommittab system table even after the retention period. Additionally, you notice the following symptoms:
-
If you connect by using Dedicated Admin Connection and query the invalid cleanup and hardened cleanup version, you will find them negative:
select * from sys.sysobjvalues where valclass = 7 and objid = 1003 -- invalid
select * from sys.sysobjvalues where valclass = 7 and objid = 1004 -- Hardened
-
If you run the following SP to clean up sys.syscommittab manually, it will not do the cleanup and will return hardened cleanup version as negative:
exec sp_flush_commit_table_on_demand
Output
The value returned by change_tracking_hardened_cleanup_version() is -nnnnnn.
-
If you collect an extended event session with event - change_tracking_cleanup during the change tracking auto cleanup, you will notice that the CleanupStatusChange is set to 32 (error) and the cleanup work exists:
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Resolution
This issue is fixed in the following cumulative updates for SQL Server:
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Note 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.
References
Learn about the terminology that Microsoft uses to describe software updates.
