Change Tracking has incorrect MINVERSION

Question

If I run this query on my DB:

SELECT CHANGE_TRACKING_CURRENT_VERSION(), CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('MyTable'))

I get:
68 and 47774 respectivley.

As far as I'm concerned this should not be possible. How can all changes since 47774 be available when the current version is only 68?

This problem is only in my dev environment where we have restored the production copy of the DB. In production the current version is greater than the min version as you would expect.

I've tried disabling and enabling table and database level change tracking but I still get the same numbers back. Basically this causes my application to reinitalised on every connection.

Is there anyway to manually reset these values? Or do I need to do anything else after a restore?

I'm running SQL 2008 R2 with SP1.

Thanks

Simon

Answer 1

Please read the following http://www.mssqltips.com/sqlservertip/2421/restoring-a-sql-server-database-that-uses-change-data-capture/

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 2

Please read the following http://www.mssqltips.com/sqlservertip/2421/restoring-a-sql-server-database-that-uses-change-data-capture/

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Thanks - but that article is about CDC - this is a change tracking problem.

Answer 3

Hi Simon,

The values of 68 and 47774 returned by  CHANGE_TRACKING_CURRENT_VERSION() and CHANGE_TRACKING_MIN_VALID_VERSION() have a conflict.You could reset the values by deleting and recreating the tracking table.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 4

Hi Peja,

How do you mean "delete and recreating the tracking table". These are large user tables which cannot be easily dropped and recreated. Besides as the MIN tracking version is a DB wide value I assume I would have to drop all at once and recreate them. I'm not convinced that would work as disabling changing tracking NULL's this value and reenabling (at DB level) starts the number again at the same value.

I'm assuming that internally the DB has a way of setting this value, If I knew how I could reset it.

Answer 5

Hi All,

I have the same problem on Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (Intel IA-64)

select

(select min(min_valid_version) from sys.change_tracking_tables) [MIN_VALID_VERSION],

CHANGE_TRACKING_CURRENT_VERSION ( ) [CURRENT_VERSION]

MIN_VALID_VERSION    CURRENT_VERSION

741 008 560                      33 793 445

Answer 6

I believe that this problem is due to us not restoring the database correctly.

Our dev and test environments are restored by taking SAN level snapshots of the production db database (taking the dev db offline, essentially switching the files, then bringing it back online).

I suspect that there is something in the recovery process of the restore command that resets the min version. I'm fairly certain in thinking that what we are doing is not supported by MS.

I've worked around the problem by blocking automatic reinitialisation in dev and test.

Simon