Log in RDS Broker database Change Tracking autocleanup is blocked on side table of

Question

Log in RDS Broker database Change Tracking autocleanup is blocked on side table of

joaomanoelc 171

HA RDS Broker - Window Server 2016 Standard
Database - SQL Server 2016 SP2 Enterprise

I have a High Availability RDS environment working very well.
But a Log is constantly recorded in the database “Change Tracking autocleanup is blocked on side table of" EnvironmentProperty ". If the failure persists, check if the table "EnvironmentProperty" is blocked by any process. ”

We disabled this item at the base of the RDS and the events stopped generating, but this caused another problem of not accessing the Apps anymore.
I saw in many other posts that the problem was solved when applying the cumulative update in SQL Server 2016 SP2, but it is not our case, as we cannot update this in production.
Does anyone have a suggestion of what they could do to eliminate this event?

Accepted answer

1 additional answer

Your answer

Answer 1

Anonymous

Hi,

This is a problem in the Microsoft products and the only fix is included in the updates for SQL Server.

If you would not like to install update, please try enabling trace flag 8293 to see if it helps.

For your reference

FIX: Change Tracking cleanup message 22123 is unexpectedly recorded in the error log file in SQL Server
https://support.microsoft.com/en-us/help/4456883/fix-change-tracking-cleanup-message-22123-is-unexpectedly-recorded-in

DBCC TRACEON - Trace Flags (Transact-SQL)
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

Enable Trace Flags in SQL Server
https://www.sqlservercentral.com/articles/enable-trace-flags-in-sql-server
Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Thanks,
Eleven

----------

If the Answer is helpful, please click "Accept Answer" and upvote it.

EDWARDS, Michael (OXLEAS NHS FOUNDATION TRUST) 1 Reputation point

2022-04-07T15:24:42.287+00:00

Hi there.

Whilst updating the SQL Server versions or turning on the suggested trace flag will mean you no longer see the error so much, it still continues. So this, to my mind, is not a fix.

The issue is so bad with our RDS implementation that the t-log is set to 12 x the size of the mdf (mdf is 2GB, ldf set to 24GB) and still runs out of space due to these errors. Of course this means that data cannot be written at that time.

We also wondered about turning change tracking off but it appears this is not a good idea.

At the time we see these errors, the log table [rds].[ErrorLog] also indicates that one of two procs are running: Session_Add_Update or ConnectionBroker_Add_Update. Is there an issue here?

Is there any more satisfactory resolution to this.

Many thanks

Mike
EDWARDS, Michael (OXLEAS NHS FOUNDATION TRUST) 1 Reputation point

2022-04-07T15:29:03.483+00:00

The error we see in the errorlog table is like this:

The transaction log for database 'XXXX' is full due to 'ACTIVE_TRANSACTION'.

Thank you

Answer 2

joaomanoelc 171

Hi
Thank you very much for your reply!

Our HA RDS Broker database is in a SQL 2016 SP2 Cluster and in this production cluster we cannot apply the cumulative update corrections.

Perhaps the alternative is to migrate that database to a new sql server 2017 cluster
I will mark your post as accepted answer

Anonymous

2020-10-12T01:40:15.627+00:00

thank you for accepting the answer

Share via

Log in RDS Broker database Change Tracking autocleanup is blocked on side table of

1 additional answer

Your answer