none
Change Tracking has incorrect MINVERSION

    Dotaz

  • 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

    2. března 2012 10:54

Odpovědi

  • 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

    • Označen jako odpověď Simon12345 29. března 2012 8:26
    29. března 2012 8:26

Všechny reakce

  • 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

    2. března 2012 11:03
  • 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.
    2. března 2012 11:21
  • 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.

    5. března 2012 7:20
  • 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.

    5. března 2012 8:49
  • 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

    6. března 2012 11:53
  • 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

    • Označen jako odpověď Simon12345 29. března 2012 8:26
    29. března 2012 8:26