T-SQL: Change Tracking Retention Period – What do we have after it expires


In the last posts we played with Change Tracking (CT):

T-SQL: Change Tracking – What is this and how it works
T-SQL: Change Tracking Retention Period – How Long the Changes are Kept?

Let’s execute few queries and see what do we have after the retention period expires.

Create database

Enable Change Tracking on database level

Create test tables

Insert test data

Check sys.change_tracking_databases

In sys.internal_tables, we can see that the CT tables are created on 2017-12-12 @ 11:53 AM

The current CT Version is

Check CHANGETABLE(CHANGES…) on 2017-12-12 @ 11:54 AM

Run the same query on 2017-12-13 @ 9:12 AM

The retention period has expired and we can’t track the changes with CT version less than or equal to 6. In a scenario in which we use CT for ETL purposes, we need to run the ETL more often than the retention period.

Update some values and check the current CT version

With the built-in function CHANGE_TRACKING_MIN_VALID_VERSION() we can determine the CT version that is reasonable to use after the retention period expires. It shows the version AFTER which the changes are kept in the database.

Lets compare the changes after version 0 (all the changes) and version 6

…and the changes after version 6

The data about the changes is lost, so we can’t track changes before version 7 anymore.

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *