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:

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