Change Tracking records which rows in a table have changed (by primary key) and whether each was an insert, update or delete, so an application or ETL process can sync only what has changed. It does not store old or new values; the reader joins back to the table for the current values. It is available in every edition.

Step 1: Enable it on the database

ALTER DATABASE [YourDB]
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

Set the retention longer than the longest gap the reader could ever have between syncs, including weekends and outages.

Step 2: Enable it on each table

ALTER TABLE dbo.YourTable
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

The table must have a primary key. TRACK_COLUMNS_UPDATED = ON also records which columns changed, at extra storage cost.

Step 3: Read the changes

-- Save this after each sync and pass it in as @last_sync next time
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version;

-- Changes since the last sync
DECLARE @last_sync bigint = 0;   -- the version saved at the previous sync
SELECT ct.SYS_CHANGE_OPERATION, ct.SYS_CHANGE_VERSION, ct.YourKeyColumn, t.*
FROM CHANGETABLE(CHANGES dbo.YourTable, @last_sync) AS ct
LEFT JOIN dbo.YourTable t ON t.YourKeyColumn = ct.YourKeyColumn;

-- If @last_sync is lower than this, cleanup has removed changes the reader needs: do a full reload
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.YourTable')) AS min_valid_version;

SYS_CHANGE_OPERATION is I, U or D. Deleted rows come back with the key only. Readers should use snapshot isolation so the version number and the data they read are consistent.

What is tracked

SELECT DB_NAME(database_id) AS database_name, is_auto_cleanup_on,
       retention_period, retention_period_units_desc
FROM sys.change_tracking_databases;

USE [YourDB];
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name, is_track_columns_updated_on
FROM sys.change_tracking_tables;

Watch the side tables

USE [YourDB];
SELECT it.name AS internal_table, OBJECT_NAME(it.parent_object_id) AS tracked_table,
       SUM(ps.row_count) AS row_count,
       SUM(ps.reserved_page_count) * 8 / 1024 AS reserved_mb
FROM sys.internal_tables it
JOIN sys.dm_db_partition_stats ps ON ps.object_id = it.object_id
WHERE it.internal_type_desc = N'CHANGE_TRACKING'
GROUP BY it.name, it.parent_object_id
ORDER BY reserved_mb DESC;

If these keep growing well beyond the retention period, auto cleanup is falling behind.

Good points

• Lightweight: it stores only keys and versions, not copies of the data.
• Every edition, no SQL Agent jobs, and cleanup is automatic.
• Simple, reliable sync logic: ‘give me everything changed since version N’.

Bad points

• Synchronous: every insert, update and delete on a tracked table also writes to a side table inside the same transaction, which adds overhead on busy tables.
• The side tables and the commit table grow, and on very busy systems auto cleanup can fall behind, slowing both the reads and the cleanup itself.
• No history: it says a row changed, not what it changed from. Intermediate changes between syncs are lost.
• Every tracked table needs a primary key.
• Readers need snapshot isolation for consistent results, which puts extra load on the tempdb version store (see Tempdb Full or Contention).
• It records what changed, not who changed it. For that, use SQL Server Audit or temporal tables.

Disable it (NEEDS APPROVAL)

Any process syncing from the table loses its sync point and will need a full reload. Rollback: enable it again, then run a full reload.

ALTER TABLE dbo.YourTable DISABLE CHANGE_TRACKING;

-- Only after it has been disabled on every table in the database
ALTER DATABASE [YourDB] SET CHANGE_TRACKING = OFF;

Change Tracking or Change Data Capture?

• Change Tracking: which rows changed and how. Synchronous, every edition, small storage. Best for ‘sync what changed’.
• Change Data Capture (CDC): full before and after values for every change, read asynchronously from the transaction log by SQL Agent capture and cleanup jobs. Standard Edition from SQL Server 2016 SP1. Best for history or data warehouse loads that need old values.
• CDC risk: the log cannot be reused until the capture job has read it (log_reuse_wait_desc = REPLICATION), so a stopped capture job fills the transaction log (see Transaction Log Full).

-- Enabling CDC, for comparison
USE [YourDB];
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
     @source_schema = N'dbo',
     @source_name   = N'YourTable',
     @role_name     = NULL;

From my SQL Server DBA handbook.