SQL Change Tracking exceeds max value

Question

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

Answer 1

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.

Answer 2

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.