SQL 2016 SP3 (build 13.0.6300.2) error with change tracking cleanup

Question

Patched a SQL 2016 Standard Edition instance to the latest SP3 (build 13.0.6300.2)

Started to get errors such as

Change Tracking autocleanup failed on side table of "MyTableName". If the failure persists, use sp_flush_CT_internal_table_on_demand to clean up expired records from its side table.

for all the CT-enabled tables on a database

If I try to use sp_flush_CT_internal_table_on_demand, I get

Cleanup Watermark = 2619327620
Internal Change Tracking table name : change_tracking_35856286
Msg 8114, Level 16, State 1, Procedure sp_add_ct_history, Line 0 [Batch Start Line 2]
Error converting data type numeric to int.
Total rows deleted: 0.
Total rows deleted: 0.

or trying sp_flush_commit_table_on_demand

The value returned by change_tracking_hardened_cleanup_version() is 2618225650.
The value returned by safe_cleanup_version() is 2619327620.

(1000 rows affected)
Msg 8114, Level 16, State 1, Procedure sp_add_ct_history, Line 0 [Batch Start Line 5]
Error converting data type bigint to int.

Notice how the version numbers are larger than what can be handled by an int

Luckily this is a QA version of the database. On Production which is still at SP2 CU17 (build 13.0.5888.1), CT clean up works without error and running those 2 individual clean up procs as above are fine.

Has anyone else experienced the same error, with large version numbers?
Any resolutions?

Answer 1

Fix has been released!

Hotfix for SQL 2016: https://support.microsoft.com/en-us/topic/kb5006943-on-demand-hotfix-update-package-for-sql-server-2016-sp3-94de2975-cd7d-47ed-b003-5d7daf4e2caf

CU for SQL 2017: https://support.microsoft.com/en-us/topic/kb5006944-cumulative-update-27-for-sql-server-2017-79117c8f-9d54-42f8-9727-5870fe475187

Answer 2

MS support have confirmed the issue

CAUSE:
SQL Server 2016 SP3 or SQL Server 2017 CU26 introduced a new table dbo.MSchange_tracking_history to record the history of change tracking cleanup. This table has a column named cleanup_version currently declared as INT datatype. One a busy system with Change Tracking enabled, the cleanup version can cross range of INT in a given interval. When change tracking cleanup runs, it will attempt to insert a BIGINT value into INT column of this table and result in the above error.

The stored procedure sys.sp_add_ct_history that interacts with dbo.MSchange_tracking_history table has a parameter @cleanup_version declared as INT.

RESOLUTION:
You can uninstall SQL Server 2016 SP3 or SQL Server 2017 CU26 if you encounter this problem.

Answer 3

Bug has been acknowledged here.

https://support.microsoft.com/en-us/topic/kb5007039-fix-you-encounter-error-messages-8114-or-22122-when-performing-change-tracking-cleanup-b102b180-a80c-4b32-90d7-0811108fe7d1

What's interesting is that the article states that although you get error 8114 when running the manual cleanup (sp_flush_CT_internal_table_on_demand), the CT tables are in fact cleaned up properly.

Answer 4

That sounds a bit alarming. As this can be difficult for other people to repro, I would recommend that you open a support case with Microsoft, so that they can investigate it. If there is a regression issue with the Service Pack, I am sure that they are very anxious to know.

Answer 5

Hi @Kevan Riley ,

Try below T-SQL again.

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database  
  
-- Fetch the tables enabled for Change Tracking  
select identity(int, 1,1) as TableID, (SCHEMA_NAME(tbl.Schema_ID) +'.'+ object_name(ctt.object_id)) as TableName  
into #CT_Tables  
from sys.change_tracking_tables  ctt  
INNER JOIN sys.tables tbl  
ON tbl.object_id = ctt.object_id  
  
-- Set up the variables  
declare @start int = 1, @end int = (select count(*) from #CT_Tables), @tablename varchar(255)  
while (@start <= @end)  
begin	  
	-- Fetch the table to be cleaned up  
	select @tablename = TableName from #CT_Tables where TableID = @start  
	-- Execute the manual cleanup stored procedure  
	exec sp_flush_CT_internal_table_on_demand @tablename   
	-- Increment the counter  
	set @start = @start + 1  
end  
drop table #CT_Tables  

If you want to open a MS support case, please refer to https://support.microsoft.com/en-us/assistedsupportproducts

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.