5

We had a large database running on a SQL Server 2014 Enterprise instance (evaluation license). Due to cost constraints, we had to move it to a new SQL Server 2017 Standard instance (per-core license; just updated to CU6). EDIT: The database is in SQL Server 2014 (120) compatibility, and was transferred by restoring full and log backups.

Everything is working fine except for change tracking. We use change tracking to locate recent changes and keep a denormalized table up to date; the denorm table is used for rapid filling of a grid for the web application.

Change tracking is working, but apparently autocleanup is not. We are getting Severity 016 alerts:

Change Tracking autocleanup is blocked on side table of tablename. If the failure persists, check if the table tablename is blocked by any process.

These come about every half hour for each of the tables that is complaining (about 4 different tables).

We've tried the manual cleanup described here ... it too presents "side table is blocked" errors.

So here are my choices:

  1. Turn off this specific alert and hope nothing blows up.
  2. Turn off autocleanup; schedule a manual cleanup during a daily maintenance window.
  3. Not sure what else I can do.

Besides "what should I do to stop this?", my side question is "why is this happening with 2017 Std when it didn't happen with 2014 Enterprise?"

  • I don't have a clue on this one, but these points may be salient: what compatibility mode is it in? How did you migrate the database? – LowlyDBA Apr 23 '18 at 19:29
  • Reading this, you might want to set up an xevent trace to get more details. – SqlWorldWide Apr 24 '18 at 3:28
  • Added to question: The database is in SQL Server 2014 (120) compatibility, and was transferred by restoring full and log backups. – Ross Presser Apr 24 '18 at 13:20
2

It seems to be a known issue and has already been recognized by Microsoft. You may read resolution as below:

Resolution

A fix for this issue is included in the following updates for SQL Server:

Cumulative Update 10 for SQL Server 2017

Cumulative Update 3 for SQL Server 2016 Service Pack 2

Cumulative Update 1 for SQL Server 2014 Service Pack 3

With this fix, you will not see continuous messages any longer. The frequency of the error 22123 messages, if any, will not be more than once every 30 minutes. To completely eliminate these messages, enable trace flag 8293.

Listed are the version which has this issues:

SQL Server 2017 DeveloperSQL Server 2017 EnterpriseSQL Server 2017 Enterprise CoreSQL Server 2017 Standard WindowsSQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise CoreSQL Server 2016 StandardSQL Server 2014 DeveloperSQL Server 2014 EnterpriseSQL Server 2014 Enterprise CoreSQL Server 2014 Standard

Please read more from link.

Hope above helps.

  • 1
    Thank you very much for the answer. As it happens, change tracking is no longer in use in this organization -- the database solution was refactored and a slow query improved, so that the need for change tracking no longer existed. – Ross Presser May 2 at 14:43
0

I am experiencing this as well. It seems these alerts are tied to an improvement to the autocleanup process in CU 2 for SQL 2017 and SP2 for SQL 2016:

https://support.microsoft.com/en-us/help/4054842/improvements-to-the-change-tracking-cleanup-process-in-sql-server

  • 1
    Which data led you to your assumption? Looking at the question I see no reference to CU 2 for SQL 2017 nor to SP 2 for SQL Server 2016. Could you please elaborate a bit? – hot2use Jun 19 '18 at 18:23
  • Well, in fact I did move from SQL 2014 to SQL 2017. We were getting the errors even with the base install, but they continued after we upgraded to CU2. I really should have done the XEvent trace ... but the truth is, we're no longer using change tracking at all. An improvement to the main query made it unnecessary to work around it with the prepopulated table. – Ross Presser Jun 19 '18 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.