Change Data Capture Tables, LSNs, Capture/Cleanup Jobs, and ETL Consumption

Learning outcomes

ServiceHub’s analytics pipeline now needs more than “row 101 changed.” It must know inserts, deletes and update images over an ordered log-derived window so it can incrementally load a warehouse. Change Data Capture (CDC) exposes those changes in relational change tables and functions keyed by log sequence numbers (LSNs). That richer contract is useful, but it still does not make an external sink exactly once: capture, consumer checkpoints, target writes and cleanup are separate operational boundaries.

1. CDC turns transaction-log changes into queryable relational rows

CDC reads relevant transaction-log records and writes captured columns plus metadata to a change table in the cdc schema. Enabling CDC at database scope creates the CDC schema and supporting objects; enabling a source table creates a capture instance, its change table, and table-valued functions. The default capture-instance name is based on the source schema and table. A source table can have at most two concurrent capture instances, which is useful during controlled schema transitions.

Each change row includes LSN metadata. __$start_lsn identifies the commit LSN associated with the change; __$seqval helps order changes that share a transaction; __$operation identifies delete, insert, or the before/after portions of updates depending on the query mode. Consumers normally establish an inclusive low/high LSN window and use cdc.fn_cdc_get_all_changes_... or the net-changes function when it was enabled.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab18') IS NULL  EXEC(N'CREATE SCHEMA lab18 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab18.CdcWorkOrder', N'U') IS NULLBEGIN  CREATE TABLE lab18.CdcWorkOrder  (    work_order_id bigint       NOT NULL PRIMARY KEY,    status        varchar(20)  NOT NULL,    priority      tinyint      NOT NULL,    description   nvarchar(200) NULL,    modified_at   datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME()  );END;GOSELECT SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('ProductVersion') AS product_version,       d.is_cdc_enabledFROM sys.databases AS dWHERE d.name = DB_NAME();GO

2. Enable the database and capture instance deliberately

USE ServiceHubLab;GOIF NOT EXISTS (SELECT 1 FROM sys.databases WHERE database_id = DB_ID() AND is_cdc_enabled = 1)  EXEC sys.sp_cdc_enable_db;GOIF NOT EXISTS(  SELECT 1  FROM cdc.change_tables  WHERE source_object_id = OBJECT_ID(N'lab18.CdcWorkOrder'))BEGIN  EXEC sys.sp_cdc_enable_table       @source_schema = N'lab18',       @source_name = N'CdcWorkOrder',       @role_name = NULL,       @supports_net_changes = 1;END;GOEXEC sys.sp_cdc_help_change_data_capture     @source_schema = N'lab18',     @source_name = N'CdcWorkOrder';EXEC sys.sp_cdc_help_jobs;GO

On boxed SQL Server, successful configuration should expose a capture instance and capture/cleanup job settings. The default cleanup retention is commonly 4,320 minutes (72 hours), but a production value must come from consumer outage tolerance, throughput, change-table size, recovery procedures and source-log pressure. sp_cdc_help_jobs reveals capture scan parameters and cleanup retention; it does not prove that the jobs are running successfully right now, so Agent state and CDC scan-session/errors also matter.

INSERT lab18.CdcWorkOrder(work_order_id,status,priority,description)VALUES (18001,'NEW',2,N'Pump inspection'),       (18002,'NEW',4,N'Gateway replacement');UPDATE lab18.CdcWorkOrderSET status='ASSIGNED', modified_at=SYSUTCDATETIME()WHERE work_order_id=18001;DELETE lab18.CdcWorkOrder WHERE work_order_id=18002;GOSELECT TOP (10)       session_id, start_time, end_time,       tran_count, command_count,       error_countFROM sys.dm_cdc_log_scan_sessionsORDER BY session_id DESC;GO

Capture is asynchronous. A statement can commit successfully before the change rows appear in the CDC tables. The ETL contract must tolerate this lag and monitor it rather than expecting CDC to be a synchronous trigger.

3. Read an LSN window and understand update semantics

USE ServiceHubLab;GODECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn(N'lab18_CdcWorkOrder');DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();SELECT @from_lsn AS from_lsn, @to_lsn AS to_lsn;SELECT __$start_lsn,       __$seqval,       __$operation,       work_order_id,       status,       priority,       description,       modified_atFROM cdc.fn_cdc_get_all_changes_lab18_CdcWorkOrder     (@from_lsn, @to_lsn, N'all update old')ORDER BY __$start_lsn, __$seqval;GO

With all update old, an update can expose both the old and new images, which is precisely the history that CT does not preserve. A net-changes function instead collapses the requested interval to one result per key and requires net-change support at capture-instance creation. Choose the function from the consumer’s semantic requirement, not because one returns fewer rows.

4. Cleanup-window loss is a data-contract failure, not “no changes”

CDC cleanup eventually removes old change rows. A consumer that stores an LSN and sleeps beyond retention cannot assume the missing rows never existed. Compare the persisted checkpoint with sys.fn_cdc_get_min_lsn(capture_instance). If the checkpoint precedes the current low endpoint, the incremental window contains a gap and the consumer needs a defined re-bootstrap/full-load procedure.

DECLARE @saved_checkpoint binary(10) = 0x00000000000000000000; -- deliberate stale simulationDECLARE @min_lsn binary(10) = sys.fn_cdc_get_min_lsn(N'lab18_CdcWorkOrder');DECLARE @max_lsn binary(10) = sys.fn_cdc_get_max_lsn();SELECT @saved_checkpoint AS saved_checkpoint,       @min_lsn AS current_low_endpoint,       @max_lsn AS current_high_endpoint;IF @saved_checkpoint < @min_lsnBEGIN  SELECT 'FULL_RELOAD_REQUIRED' AS decision;  -- Rebuild destination from an authoritative snapshot, then store a fresh checkpoint.ENDELSEBEGIN  SELECT 'INCREMENTAL_WINDOW_VALID' AS decision;END;GO

The intentionally stale all-zero binary value makes the defensive branch visible without changing the cleanup job or deleting real CDC rows. In production, retain both the checkpoint and consumer identity, capture instance, schema version, last successful target commit, and reconciliation evidence.

“Exactly once” across SQL Server and an unrelated external warehouse is not a property CDC supplies. If a consumer commits SQL Server checkpoint state separately from an external target transaction, a crash can create duplicates or loss. Common patterns use idempotent target keys/upserts, an external transactional checkpoint colocated with the target, deduplication keys, replay, and periodic reconciliation.

5. Production judgment and bridge

CDC is appropriate when downstream systems need relational change rows and LSN windows, often for ETL or event extraction. Operate its capture latency, Agent jobs, cleanup, low/high endpoints, schema evolution and checkpoint recovery as one system. Do not expose raw CDC tables as an eternal public API without ownership. Lesson 3 changes direction from log-derived capture to native transactional messaging with Service Broker.

Authoritative references

• What is Change Data Capture (CDC)?
• Enable and disable CDC
• sys.sp_cdc_help_jobs
• sys.sp_cdc_add_job
• SQL Server 2025 editions and supported features