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:
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO CREATE DATABASE PL; GO USE PL; GO
Enable Change Tracking on Database Level:
ALTER DATABASE [PL] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS); GO
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:
CREATE TABLE dbo.ChangeTrackingYes ( ChangeTrackingYesID INT IDENTITY(1, 1) PRIMARY KEY , [Value] NVARCHAR(128) ); GO ALTER TABLE dbo.ChangeTrackingYes ENABLE CHANGE_TRACKING; GO -- dbo.ChangeTrackingColumnsYes tracks columns updates CREATE TABLE dbo.ChangeTrackingColumnsYes ( ChangeTrackingColumnsYesID INT IDENTITY(1, 1) PRIMARY KEY , Value1 NVARCHAR(128) , [Value2] NVARCHAR(128) ); GO ALTER TABLE dbo.ChangeTrackingColumnsYes ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); GO
Insert data in the tracked tables:
-- Insert into tracked table INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'AI'; INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'BI'; INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'CI'; GO -- Insert into tracked columns table INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'AI', 'XI'; INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'BI', 'YI'; INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'CI', 'ZI'; GO
Check Object Catalog:
SELECT * FROM sys.change_tracking_databases; GO

and the current CT Version:
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion; GO

Check when the tracked tables were created:
SELECT OBJECT_SCHEMA_NAME([object_id]) AS SchemaName , '<--->' AS ' ' , * FROM sys.internal_tables WHERE parent_object_id IN ( SELECT [object_id] FROM sys.change_tracking_tables ); GO

The create_date in my case is 2017-11-10. The versions are:
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT; SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT; GO

I will wait few days and will check this again… OK, let’s check the versions on 2017-11-13:
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT; SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT; GO

Wait until tomorrow… and the versions are:
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT; SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT; GO

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 :-)