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 theretention 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 -- invalidselect * 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_demandOutputThe 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:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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 tounderstand the root cause of why the cleanup version became negative, it is recommended that you call up support and provide more details.