CHANGE_TRACKING_CURRENT_VERSION is stuck in replica DBs

Question

CHANGE_TRACKING_CURRENT_VERSION is stuck in replica DBs

Roi Sanchez Riveira 20

We have configured some databases in a Business Critical elastic pool

And on those, we have change tracking enabled in a few tables.

We use that feature to detect data changes (inserts, updated, etc) to our data and refresh some other resources downstream, and to reduce load in the primary database, we are reading the change track information from the replica (Business Critical allows to read from a replica if you use applicationIntent=ReadOnly as part of the connection string of your app).

Recently we have found that our downstream process has stopped updating the data, and after a look, we're seeing that the values returned by CHANGE_TRACKING_CURRENT_VERSION are stuck in the ReadOnly replica, we also have a standby configured in another server, and it's stuck there too.

All the data is being replicated fine, and the results of CHANGE_TRACKING_MIN_VALID_VERSION are the same on all the queries, it's just CHANGE_TRACKING_CURRENT_VERSION that seems stuck.

Have anyone of you found this issue, and know any way to fix it?

There was an Azure initiated maintenance on the night before this was noticed, but we can't say for sure that it triggered this issue.

I haven't tried any particular fix, as the databases are replicating fine, and the replication is in Azure hands, there is no way for me to replicate on a separate environment to try fixes and I haven't found any documentation to help me troubleshoot

Saraswathi Devadula 10,855 Reputation points Microsoft External Staff Moderator

2025-07-31T11:41:13.5633333+00:00

Hello Roi Sanchez Riveira
I kindly request you to please share the details requested in the private message for further troubleshooting.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-31T13:09:34.26+00:00

This certainly sound suspect, and you should share the data Saraswathi requested so this can be investigated from the backend.

Just one check: Have you verified that changes after CHANGE_TRACKING_CURRENC_VERSION are actually available on the secondaries?
Roi Sanchez Riveira 20 Reputation points

2025-07-31T14:27:56.72+00:00

@Erland Sommarskog Yes, that's the first thing I've checked, the secondaries are up to date, all other changes are being replicated, number of rows of all tables match (I haven't checked the data on detail, but matching number of rows should be enough to confirm that replica is good)

Also replica status show as healthy

Accepted answer

0 additional answers

Your answer

Saraswathi Devadula 10,855 Reputation points Microsoft External Staff Moderator

2025-07-31T11:41:13.5633333+00:00

Hello Roi Sanchez Riveira
I kindly request you to please share the details requested in the private message for further troubleshooting.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-31T13:09:34.26+00:00

This certainly sound suspect, and you should share the data Saraswathi requested so this can be investigated from the backend.

Just one check: Have you verified that changes after CHANGE_TRACKING_CURRENC_VERSION are actually available on the secondaries?
Roi Sanchez Riveira 20 Reputation points

2025-07-31T14:27:56.72+00:00

@Erland Sommarskog Yes, that's the first thing I've checked, the secondaries are up to date, all other changes are being replicated, number of rows of all tables match (I haven't checked the data on detail, but matching number of rows should be enough to confirm that replica is good)

Also replica status show as healthy

Answer 1

Hello Roi Sanchez Riveira
Following our discussion, change tracking functionality is not supported on secondary replica as documented,

Replication, Change Tracking, Change Data Capture & Availability Groups - SQL Server Always On | Microsoft Learn

Can we access Change Data Capture and Change Tracking on readable replica? | Microsoft Community Hub

If you try to run following query on your readonly replica.

SELECT * FROM changetable(changes tableName, 0) as ct

It will give you the error,

Msg 22117, Level 16, State 1, Line 7 For databases that are members of a secondary availability replica, change tracking is not supported. Run change tracking queries on the databases in the primary availability replica.

The value of CHANGE_TRACKING_CURRENT_VERSION() version is indeterministic on secondary replica. It may give you right value but there is no expectation as the feature is unsupported.

We will improve our error handling by throwing correct error for CHANGE_TRACKING_CURRENT_VERSION()

Roi Sanchez Riveira 20 Reputation points

2025-08-07T11:55:29.7466667+00:00

Agreed with the answer here, it's not an ideal solution as being able to depend on some of that information for our app to be aware of the changes and reduce the load on our primary would be great. But I understand now that the feature is not there, and what we were just lucky that the current version was accurate/useful for some time.

We have to rework our application with this on mind, so it didn't fully solve our problem, but it provided an explanation for it.

Share via

CHANGE_TRACKING_CURRENT_VERSION is stuck in replica DBs

0 additional answers

Your answer