7

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?"

Improve this question
1
  • Reading this, you might want to set up an xevent trace to get more details.
    – SqlWorldWide
    Apr 24, 2018 at 3:28

2 Answers 2

Reset to default
4

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 Developer
  • SQL Server 2017 Enterprise
  • SQL Server 2017 Enterprise Core
  • SQL Server 2017 Standard Windows
  • SQL Server 2016 Developer
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Enterprise Core
  • SQL Server 2016 Standard
  • SQL Server 2014 Developer
  • SQL Server 2014 Enterprise
  • SQL Server 2014 Enterprise Core
  • SQL Server 2014 Standard

Please read more from link.

Hope above helps.

Improve this answer
2
  • 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, 2019 at 14:43
  • Didn't solve the issue, Issue still exists in Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37
    – Mamdouh
    Aug 11, 2022 at 4:57
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

Improve this answer
2
  • 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?
    – John K. N.
    Jun 19, 2018 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, 2018 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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