T-SQL: Change Tracking Retention Period – How Long the Changes are Kept

As we mentioned in Change Tracking – What is this and how it works, the retention period is the one that determines how long the changes are kept.

Create database:

Enable Change Tracking on Database Level:

After the Change Tracking is enabled on database level, we can edit its properties in SSMS. Right click on the database –> Properties:

and then Change Tracking:

The Retention Period Units could be:

  • Days
  • Hours
  • Minutes

Create tables:

Insert data in the tracked tables:

Check Object Catalog:

and the current CT Version:

Check when the tracked tables were created:

The create_date in my case is 2017-11-10. The versions are:

I will wait few days and will check this again… OK, let’s check the versions on 2017-11-13:

Wait until tomorrow… and the versions are:

No data. This means that we need to take action before the retention period has expired in order to use Change Tracking.

Keep it simple :-)