Did the Change Tracking internals change from SQL Server 2008 to 2012?

Question

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:

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Employee'))

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?

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.

CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)

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'