question

KevanRiley-0803 avatar image
0 Votes"
KevanRiley-0803 asked KevanRiley-0803 commented

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

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?

sql-server-general
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have reverted the server back to SP2 CU17, and the errors stop.
That to me is clear that the error is introduced in SP3.

0 Votes 0 ·

Hello,
did you get any further with this? I'm seeing the same when testing the latest SQL 2017 CU too
Microsoft SQL Server 2017 (RTM-CU26) (KB5005226) - 14.0.3411.3
I have raised this through Microsoft support.

0 Votes 0 ·

Not really - have raised it through MVP channels, but still think that it will come down to raising a support case, which I cannot do!

So, glad (unfortunately) to hear that it's also happening on SQL2017 and that you can replicate.
Let me know if you hear anything.

0 Votes 0 ·
Show more comments
KevanRiley-0803 avatar image
1 Vote"
KevanRiley-0803 answered KevanRiley-0803 commented
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Great! Thanks for the update!

0 Votes 0 ·

Thanks to you too for supporting this

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered KevanRiley-0803 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What's the best way to do that?
I have Support incidents with my Partner Support contract, but it doesn't cover SQL 2016 - only current version and previous one!

0 Votes 0 ·

I will have to admit that I don't know. I'm not in a role where I open support cases myself. I hope that someone else can chime in.

0 Votes 0 ·

Thanks - who'd have thought it was so hard to tell someone :)

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ErlandSommarskog commented

Hi @KevanRiley-0803,

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.



· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, but that code gives the same error.

I cannot open a support case - the support website will not allow me to use my Support plan for SQL 2016
133891-image.png


0 Votes 0 ·
image.png (91.5 KiB)

So if you could tell me how to best raise this issue, I will happily follow that

0 Votes 0 ·

Hi @KevanRiley-0803,

It seems that there is some issue with your contract. I am not familiar this. Suggest you asking help for the vender who sell you the contract.

0 Votes 0 ·
Show more comments

The reason you cannot open a support case, may be found here: https://docs.microsoft.com/sv-se/lifecycle/products/sql-server-2016

According to this page, SQL 2016 went out of mainstream support on 2021-07-13! So you would need to have an extended support, which I believe comes with an extra cost, to open a case. For a service pack released this week.

You have my full understanding if you prefer to stay on SQL 2016 SP2 CU17.

0 Votes 0 ·
NickGuy avatar image
1 Vote"
NickGuy answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KevanRiley-0803 avatar image
1 Vote"
KevanRiley-0803 answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks to both Kevin and Nick for keeping us updated!

0 Votes 0 ·