Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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? – Max Vernon Aug 29 '13 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. – Glenn Estrada Aug 29 '13 at 15:57
3  
With repro steps for the issue, please report it on Connect so they can fix it! – Jon Seigel Aug 29 '13 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. – Guillaume R. Jan 27 at 17:12

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'

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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