SQL Change Tracking exceeds max value

Kapil Vagyani 21 Reputation points
2021-07-15T19:17:01.817+00:00

Hello,

we got change monitoring that usage SQL Change Tracking feature internally. MSDN document says CHANGE_TRACKING_VERSION value data type is 'bigint'. so wondering what happens if it reaches max limit .. Does SQL reset version next sequence value to 'zero'? how to respond if value reached its max limit? will SQL Server able to track changes?

Also is there interface or sql function to reinitialize the SQL Change Tracking value to its min or zero?

Thanks in advance!
Kapil

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,178 questions
Accepted answer
  1. Cris Zhan-MSFT 6,566 Reputation points
    2021-07-16T03:47:59.487+00:00

    Hi,

    The change tracking version is an 8-byte integer (BIGINT) that reflects the most recent change ID in the database.
    The range of bigint is [-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)], which is almost impossible to reach the maximum value for Change Tracking version(starting from 0).

    There is no way to reset/clear the current version for change tracking. Disabling and restarting change tracking also does not reset the change tracking version.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 72,971 Reputation points MVP
    2021-07-16T08:55:27.46+00:00

    My guess is that there is a hard error.

    However, before you sound the alarm, look at the response in this thread from Roger Wolf: https://stackoverflow.com/questions/26459935/what-happens-to-change-tracking-value-when-it-reaches-bigint-limit

    In order to reach bigint limit, you have to change 1 million rows in your database every second during the next 292471 years. Good luck reaching it.