Error Messages 8114 or 22122 When Performing Change Tracking Cleanup
You’re a data professional and you’re trying to keep up with patching a wide range of SQL Server versions and editions. How do you know what’s in the CU and whether you should apply it or not? My favorite way to read up on CUs is to go to SQLServerUpdates and click around there. It will take you to the latest CU pages as well as give you a way to see previous CUs that are available.
While doing this recently, I discovered this for CU 26 on SQL Server 2017.
Known Issues SQL Server 2017 CU 26
How to check If Change Tracking Is Enabled
SQL Server 2017 CU 26 has a known issue with change tracking. Before you apply that CU, be sure to check that you’re not using Change Tracking somewhere. Well, how would you do that if you have a larger environment and don’t immediately know if you’re using Change Tracking somewhere?
You could connect to your Central Management Server in SQL Server Management Studio and then run the below to return the SQL Server instance and database where the Change Tracking Feature is enabled. In this case, we don’t care what tables are involved in the Change Tracking, so there is no need to look at sys.change_tracking_tables. We only need to know that Change Tracking is enabled. This is enough to know that we shouldn’t update to SQL Server 2017 CU26 for that SQL instance.
SELECT db_name(database_id) AS DBname FROM sys.change_tracking_databases;
Error Messages 8114 or 22122 When Performing Change Tracking Cleanup
You might see this:
“Msg 8114, Level 16, State 1, Procedure sp_add_ct_history, Line LineNumber
Error converting data type numeric to int.”
Or this error:
“DateTime spidNum Error: 22122, Severity: 16, State: 1.
DateTime spidNum Change Tracking autocleanup failed on side table of “table_name”. If the failure persists, use sp_flush_CT_internal_table_on_demand to clean up expired records from its side table.“
The solution is to either stay on CU25 or lower, or go up to CU 27 for SQL Server 2017. Microsoft specifically says on this page that CU27 has a fix. You could also uninstall CU26 for SQL Server 2017. You might need to do this if you’re already on this version and can’t update to CU27 for some reason. To do this you can go to Control Panel > Programs and Features > View Installed updates
If you’re on SQL Server 2016 SP3 and experiencing this problem, there is a hotfix here listed as KB5006943.