In trouble shooting an issue with synchronizing disconnected devices with a central database server, we are experiencing a problem after upgrading to SQL Server 2012 on the server. It appears that the CHANGE_TRACKING_MIN_VALID_VERSION is returning a value 1 higher than it should (or at least than it did prior to the upgrade.)

I have been working thru Arshad Ali's great walk thru example of how to set up a simple example.

I have run the scripts from #1 thru #5 to insert, delete, and update a row in the Employee table in both a SQL Server 2008 and a 2012 environment.

In 2008, the following statement returns a 0:


In 2012, it returns a 1.

In working thru a few more scripts (6-8) in the tests, I set the retention period to 1 minute to hopefully force a cleanup action. I left for the day and apparently it ran overnight.

In the 2008 instance, the CHANGE_TRACKING_CURRENT_VERSION and the CHANGE_TRACKING_MIN_VALID_VERSION are equal (11). In the 2012 instance, the CHANGE_TRACKING_MIN_VALID_VERSION is one higher (12) than the CHANGE_TRACKING_CURRENT_VERSION (11). This could have an impact to the synchronization process when a database is idle for extended periods of time. And we have found that process could get caught in a loop, especially when the following test is performed to determine if a re-initialization, as opposed to synchronization, is required:

IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Employee')) > @sync_last_received_anchor 
       RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''...

Has anyone else experienced this change in behavior? Does anyone have an explanation?

  • 2
    There is a Microsoft Connect item for this issue, connect.microsoft.com/SQLServer/feedback/details/770014/… basically Microsoft thinks the issue might be related to corruption in the database in question. Can you repro this situation in a newly created database?
    – Hannah Vernon
    Aug 29, 2013 at 13:16
  • 1
    Max, I did review the Connect article. Unfortunately, the original poster appears to have abandoned the discussion and MS closed the issue. When setting up the reproduction of the issue, I started the testing with newly created databases in both a 2008R2 and 2012 instance. Both databases appear to be functioning normally in all other aspects. Aug 29, 2013 at 15:57
  • 3
    With repro steps for the issue, please report it on Connect so they can fix it!
    – Jon Seigel
    Aug 29, 2013 at 16:32
  • Did you change the Compatibility Level of the DB after upgrade ? I don't use change tracking, but think about a version mismatch after upgrading. Jan 27, 2016 at 17:12

1 Answer 1


One doesn’t use the min_valid_version to track the changes. This is only used to validate if your client has to be reinitialized, if the metadata has been cleaned up before client could consume the changes.


Obtains the minimum version that is valid for use in obtaining change tracking information from the specified table when you are using the CHANGETABLE function.

Min_valid_version changes with the cleanup version and doesn’t depend on the changes to the user table. Every time cleanup thread runs there could be an update to min_valid_version irrespective of data changes.

Previous to 2012, min_valid_version was marked same as cleanup version, when in fact it should be one more than the cleanup version as metadata for that version has already been cleaned up. In 2012 that is what they changed to make sure they update the right min_valid_version.

One should not be tracking change using min_valid_version, instead should be saving the last_sync_version after every sync and call the CHANGETABLE to enumerate the changes after the last sync version.

By design - Min valid version changes with the cleanup version and doesn’t depend on the changes to the user table. Every time cleanup thread runs there could be an update to min valid version irrespective of data changes.

Resolve - Change procedure to use 'current_version' instead of 'min_valid_version'

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.