Change Data Capture (CDC)

October 15, 2026

Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Change Data Capture reads the transaction log and copies every insert, update and delete on a tracked table into a change table, with the old and new column values. Unlike Change Tracking, which only records which keys changed, CDC keeps the data itself, so it suits ETL feeds and audit-style history. It needs SQL Server Agent (a capture job and a cleanup job) and is available in Enterprise, and in Standard from SQL Server 2016 SP1.

Step 1: Enable it on the database

USE [YourDB];
EXEC sys.sp_cdc_enable_db;

This needs sysadmin. It creates the cdc schema and the cdc user in the database.

Step 2: Enable it on each table

USE [YourDB];
EXEC sys.sp_cdc_enable_table
    @source_schema        = N'dbo',
    @source_name          = N'YourTable',
    @role_name            = NULL,     -- or a role that controls who can read the change data
    @supports_net_changes = 1;        -- needs a primary key or unique index

The first table enabled in a database creates two Agent jobs: cdc.YourDB_capture and cdc.YourDB_cleanup. Each table gets a change table called cdc.dbo_YourTable_CT.

Step 3: Read the changes

USE [YourDB];
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn(N'dbo_YourTable'),
        @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

-- Every change, with before and after images for updates
SELECT __$operation, __$start_lsn, *
FROM cdc.fn_cdc_get_all_changes_dbo_YourTable(@from_lsn, @to_lsn, N'all update old');

-- One row per key showing its final state in the range
SELECT __$operation, *
FROM cdc.fn_cdc_get_net_changes_dbo_YourTable(@from_lsn, @to_lsn, N'all');

__$operation is 1 for a delete, 2 for an insert, 3 for the row before an update and 4 for the row after it. To start from a point in time rather than the beginning, convert the time to an LSN:

USE [YourDB];
SELECT sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2026-10-01T00:00:00');

What is enabled

SELECT name, is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;

USE [YourDB];
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, name AS table_name
FROM sys.tables WHERE is_tracked_by_cdc = 1;

EXEC sys.sp_cdc_help_change_data_capture;
EXEC sys.sp_cdc_help_jobs;

Health checks

USE [YourDB];
-- Recent capture sessions: latency is in seconds, error_count should be 0
SELECT TOP (10) session_id, start_time, end_time, duration, tran_count,
       command_count, latency, error_count
FROM sys.dm_cdc_log_scan_sessions
ORDER BY start_time DESC;

SELECT * FROM sys.dm_cdc_errors;

-- REPLICATION here means the capture job is behind or stopped and the log cannot be reused
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = N'YourDB';

Retention

The cleanup job keeps three days of changes by default. The value is in minutes:

USE [YourDB];
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;   -- 7 days

Watch out for

If the capture job stops, the transaction log cannot be truncated and will grow until it fills. Alert on the job.
Columns added to a table after CDC was enabled are not captured. Create a second capture instance for the new shape, move the readers over, then drop the old instance.
Restoring a CDC database to another server drops CDC unless you restore WITH KEEP_CDC, and the Agent jobs must be recreated with sys.sp_cdc_add_job.
In an Availability Group the capture and cleanup jobs must exist on every replica that can become primary.
Change tables can grow quickly on busy tables. Check their size alongside the cleanup retention.

Removing it

USE [YourDB];
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'YourTable',
                              @capture_instance = N'all';
EXEC sys.sp_cdc_disable_db;

From my SQL Server DBA handbook.

T-SQL

Clive Roberts