Summary
This update contains the following improvements to the change tracking cleanup feature in Microsoft SQL Server 2016 and 2017 on Windows.
-
The cleanup of the syscommittab table at a hard checkpoint is avoided, because this might have impact on the performance of checkpoint operations. Instead, the syscommittab table may be cleaned as soon as the change tracking auto cleanup is completed. When the change tracking auto cleanup is completed, it updates watermark version – hardened cleanup version. This hardened cleanup version is used in the syscommittab cleanup to identify the records to be removed.
-
Currently, in the change tracking auto cleanup process, whenever the cleanup thread experiences blocking while deleting or cleaning rows from a side table, the auto cleanup stops and exits from the process. With this update, instead of exiting from the process, the problematic tables are marked for retries and proceed with remaining tables in the list. When the thread finishes cleaning up the final table, just before it updates the hardened cleanup version, the process will be retried to check if there are any tables that are marked for retries.
-
Additionally, the following sub events are added to the existing change_tracking_cleanup XEvent.
a) ErrorInTableCleanup – This helps identify if the change tracking cleanup experiences any issues when it cleans up expired entries from any of the side table.
b) RowsCleanedupInLoop – This helps keep track of the change tracking auto cleanup progress at a table level.
c) RetryCleanup – This helps identify the retries that are made by the change tracking auto cleanup thread.
Resolution
This update is included in the following cumulative update and service pack 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:
Service pack information for SQL Server 2016
Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You do not have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.
How to determine the version, edition and update level of SQL Server and its components
References
Learn about the terminologythat Microsoft uses to describe software updates.
