Change Data Capture in SQL Server: Setup, Limits, Streaming

SQL Server's change data capture (CDC) turns the transaction log into queryable change tables: every insert, update and delete on a tracked table, with column values, in commit order. Enabling it takes two stored procedures. Running it in production is where the work is: two SQL Server Agent jobs, a three-day default retention window, a transaction log that can't truncate past changes nobody has read, and capture instances that ignore columns added after you enabled them.

This guide covers the full path, from sys.sp_cdc_enable_db to a continuous feed into a warehouse. SQL Server behavior here follows Microsoft's documentation for SQL Server 2017 through 2025 and Azure SQL Managed Instance. Azure SQL Database and Amazon RDS differ where noted.

How to enable change data capture in SQL Server

Check four things first:

• Edition. CDC is in Enterprise and Standard editions and their Developer equivalents. Express has neither CDC nor SQL Server Agent.
• SQL Server Agent is running. On SQL Server and Azure SQL Managed Instance, capture and cleanup run as Agent jobs. On Linux, Agent ships with mssql-server but is disabled by default: run sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true and restart the service.
• Permissions. Enabling CDC on a database requires sysadmin. Enabling it on a table requires db_owner.
• Nothing is already named cdc. CDC creates a cdc schema and a cdc user. If either exists, sys.sp_cdc_enable_db fails.

Then enable the database, then each table:

USE SalesDb;
GO

-- Creates the cdc schema, the cdc user and the metadata tables. Requires sysadmin.
EXEC sys.sp_cdc_enable_db;
GO

-- Creates a capture instance for dbo.orders. Requires db_owner.
EXEC sys.sp_cdc_enable_table
    @source_schema        = N'dbo',
    @source_name          = N'orders',
    @role_name            = NULL,  -- required; NULL means no gating role
    @supports_net_changes = 1;     -- needs a primary key or @index_name
GO

The second call creates a capture instance named dbo_orders, a change table cdc.dbo_orders_CT, and two query functions: cdc.fn_cdc_get_all_changes_dbo_orders and cdc.fn_cdc_get_net_changes_dbo_orders. Because it's the first table in the database, it also creates the cleanup job and, unless the database has transactional publications, the capture job.

Check what's enabled:

SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = DB_NAME();

EXEC sys.sp_cdc_help_change_data_capture;  -- capture instances and captured columns
EXEC sys.sp_cdc_help_jobs;                 -- capture and cleanup job settings

Three sys.sp_cdc_enable_table options are worth deciding before the first run:

• @captured_column_list captures a subset of columns, for privacy or volume. Primary key columns must be in the list.
• @filegroup_name puts the change table in its own filegroup, which Microsoft recommends. If that filegroup fills up, the capture job fails with error 1105 and stops capturing until space is available.
• @index_name identifies rows by a unique index instead of the primary key. If you leave it out, CDC uses the table's primary key, and you can't change that key later without disabling CDC on the table.

On Amazon RDS for SQL Server, enable the database with EXEC msdb.dbo.rds_cdc_enable_db 'SalesDb'; instead. After that, any db_owner can enable tables with sys.sp_cdc_enable_table. Azure SQL Database runs sys.sp_cdc_enable_db too, but needs only db_owner, and you connect to the database directly because USE isn't available there.

How SQL Server CDC works: the log, the jobs and retention

Every change to a tracked table is already in the transaction log. CDC adds a reader. The capture job calls sp_replcmds, the same internal log reader that transactional replication uses, and writes each change into the table's change table. If the database also has transactional publications, the replication Log Reader Agent fills the change tables instead and no capture job is created.

Each change row starts with metadata columns, followed by the captured source columns:

• __$start_lsn is the commit log sequence number (LSN). Changes from one transaction share it, and it orders transactions.
• __$seqval orders changes within a transaction.
• __$operation is 1 for delete, 2 for insert, 3 for the update before-image and 4 for the update after-image.
• __$update_mask is a bitmask of which captured columns changed.

Inserts and deletes are one row each; an update is two.

Two Agent jobs keep it running, named cdc.SalesDb_capture and cdc.SalesDb_cleanup for this database:

• Capture starts immediately and runs continuously, waiting 5 seconds between log scan cycles by default.
• Cleanup runs daily at 2 A.M. and deletes change rows older than the retention period: 4,320 minutes (three days) by default, at most 5,000 rows per delete statement.

Three days is the number to plan around. If whatever reads the change tables is down longer than that, the changes it missed are deleted, and the only way back is a fresh snapshot of the table. Retention is set in minutes, up to 52,494,800 (100 years), and one value applies to every capture instance in the database:

-- Keep seven days of change data instead of three.
EXEC sys.sp_cdc_change_job
    @job_type  = N'cleanup',
    @retention = 10080;
GO

Both jobs read their settings when they start. Cleanup starts on its daily schedule, so new retention applies from its next run. Capture runs continuously, so restart it after changing @maxtrans, @maxscans or @pollinginterval. Stopping the capture job pauses the log scan; it doesn't lose changes.

EXEC sys.sp_cdc_stop_job  @job_type = N'capture';
EXEC sys.sp_cdc_start_job @job_type = N'capture';

Those three settings also cap throughput: (maxtrans × maxscans) divided by the polling interval bounds the average number of transactions the capture job can process per second. If capture latency climbs on a busy database, look there first.

Reading the change tables with LSNs

Query through the generated functions rather than the _CT table. They take an LSN range and reject ranges outside what's still retained.

DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn(N'dbo_orders');
DECLARE @to_lsn   BINARY(10) = sys.fn_cdc_get_max_lsn();

SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS committed_at,
    CASE __$operation
        WHEN 1 THEN 'delete'
        WHEN 2 THEN 'insert'
        WHEN 3 THEN 'update (before)'
        WHEN 4 THEN 'update (after)'
    END AS operation,
    *
FROM cdc.fn_cdc_get_all_changes_dbo_orders(@from_lsn, @to_lsn, N'all update old')
ORDER BY __$start_lsn, __$seqval;

N'all' returns only the after-image of an update. N'all update old' returns both. With @supports_net_changes = 1, cdc.fn_cdc_get_net_changes_dbo_orders returns one row per changed key with its final state in the range, which is often what a batch load wants. Net changes add an index to the change table, and Microsoft notes that can slow capture.

For continuous reads, keep a checkpoint: store the last LSN you applied downstream, start the next read one LSN past it with sys.fn_cdc_increment_lsn, and advance the checkpoint only after the batch is written.

-- One row per capture instance: the last LSN applied downstream.
CREATE TABLE dbo.cdc_checkpoint (
    capture_instance SYSNAME    NOT NULL PRIMARY KEY,
    last_lsn         BINARY(10) NOT NULL
);
GO

DECLARE @last_lsn BINARY(10) =
    (SELECT last_lsn FROM dbo.cdc_checkpoint WHERE capture_instance = N'dbo_orders');
DECLARE @min_lsn  BINARY(10) = sys.fn_cdc_get_min_lsn(N'dbo_orders');
DECLARE @to_lsn   BINARY(10) = sys.fn_cdc_get_max_lsn();
DECLARE @from_lsn BINARY(10) =
    CASE WHEN @last_lsn IS NULL THEN @min_lsn
         ELSE sys.fn_cdc_increment_lsn(@last_lsn) END;

-- Cleanup removed changes this reader never saw: re-snapshot the table.
IF @from_lsn < @min_lsn
    THROW 50001, N'Change data for dbo_orders was cleaned up before it was read.', 1;

IF @from_lsn <= @to_lsn
BEGIN
    SELECT *
    FROM cdc.fn_cdc_get_all_changes_dbo_orders(@from_lsn, @to_lsn, N'all')
    ORDER BY __$start_lsn, __$seqval;

    -- Keep this for step 2.
    SELECT @to_lsn AS read_through_lsn;
END

Write that batch downstream. Only once it's committed there, advance the checkpoint as a separate step, passing in the read_through_lsn from the read:

-- Step 2: run only after the batch is committed downstream.
-- @read_through_lsn is the value the read returned.
MERGE dbo.cdc_checkpoint AS c
USING (SELECT N'dbo_orders' AS capture_instance, @read_through_lsn AS last_lsn) AS s
    ON c.capture_instance = s.capture_instance
WHEN MATCHED THEN UPDATE SET last_lsn = s.last_lsn
WHEN NOT MATCHED THEN INSERT (capture_instance, last_lsn)
    VALUES (s.capture_instance, s.last_lsn);

The @from_lsn < @min_lsn guard in the read matters because the window moves. Cleanup raises the low end every day, and a range that falls outside the window fails with error 313, whose message ("An insufficient number of arguments were supplied for the procedure or function") says nothing about LSNs. To read by time instead, convert with sys.fn_cdc_map_time_to_lsn, using 'smallest greater than' for the start of the range and 'largest less than or equal' for the end.

Some column types need care downstream. Computed columns are always NULL in change data. text, ntext and image columns are NULL in delete and before-update rows. varchar(max), nvarchar(max) and varbinary(max) are NULL in before-update rows unless that column changed.

CDC vs Change Tracking vs temporal tables

SQL Server has two other built-in ways to answer "what changed":

For a warehouse feed, CDC is the one that hands you every change with its values, deletes included, without querying the source tables again. Change tracking is lighter, but each sync joins back to the source tables and only sees the latest state. Temporal tables keep history for FOR SYSTEM_TIME queries inside SQL Server; they aren't a feed. CDC and change tracking can run on the same database.

Limitations that bite in production

Schema changes need a new capture instance

A capture instance's column list is fixed when you create it. After ALTER TABLE ... ADD, the existing instance ignores the new column. A dropped column comes through as NULL. Data type changes are carried into the change table, but some of them make the capture scan fail, with the error in sys.dm_cdc_errors: converting a string column to date (241), int (245) or a numeric type (8114), narrowing bigint to int (8115), or shrinking a column (2628). For 2628 and 8115, Microsoft's fix is to consume the pending changes first, then disable and re-enable CDC on the table; for the others, it's to disable and re-enable CDC. Every DDL change to a tracked table is recorded in cdc.ddl_history, which sys.sp_cdc_get_ddl_history reads.

To capture a new column, create a second capture instance. A table can have at most two at once, so the rotation is: create v2, keep reading v1 up to the LSN where v2 starts, switch readers, drop v1.

ALTER TABLE dbo.orders ADD discount_code VARCHAR(32) NULL;
GO

-- A second capture instance with the new column list.
EXEC sys.sp_cdc_enable_table
    @source_schema    = N'dbo',
    @source_name      = N'orders',
    @role_name        = NULL,
    @capture_instance = N'dbo_orders_v2';
GO

-- Once the capture job has picked up v2: read dbo_orders up to
-- just before this LSN, then read dbo_orders_v2 from it.
SELECT sys.fn_cdc_get_min_lsn(N'dbo_orders_v2') AS v2_start_lsn;

-- When no reader needs the old instance:
EXEC sys.sp_cdc_disable_table
    @source_schema    = N'dbo',
    @source_name      = N'orders',
    @capture_instance = N'dbo_orders';
GO

One more DDL trap: if you add a NOT NULL column with a default, the table fills existing rows with the default, but CDC reports NULL for them. Microsoft's workaround is a no-op UPDATE on those rows or a rebuild of the clustered index.

The transaction log can't truncate past unread changes

The log truncation point doesn't advance until the capture process has harvested every change marked for capture, even under the simple recovery model, and CHECKPOINT won't free space while capture is stopped. A stopped Agent, a failing capture job and a capture job that can't keep up all end the same way: a growing log, and eventually error 9002, log full.

-- With CDC on and no transactional replication, REPLICATION here
-- means capture is behind or not running.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

-- Most recent nonzero capture latency, in seconds. (In this row,
-- the other counters are totals across all scan sessions.)
SELECT latency
FROM sys.dm_cdc_log_scan_sessions
WHERE session_id = 0;

-- Errors from recent capture scans.
SELECT * FROM sys.dm_cdc_errors;

If you run accelerated database recovery (ADR), enabling CDC turns off ADR's aggressive log truncation. The two can't be combined at all on SQL Server 2019; that's supported from SQL Server 2022 CU18.

SQL Server Agent is a hard dependency

You can enable CDC on a table while Agent is stopped, so the setup looks fine while nothing is captured. CDC also fails when the Database Engine or Agent service runs as NETWORK SERVICE (error 22832). And the jobs don't follow the database everywhere, because they live in msdb. A restore to another server disables CDC unless you use KEEP_CDC, and even with it you recreate the capture and cleanup jobs there with sys.sp_cdc_add_job; until you do, the log waits on REPLICATION. In an Always On availability group, the replica that becomes primary needs the jobs too, and after a database mirroring failover you recreate them on the new principal.

Operations CDC misses or blocks

• Partition switches aren't captured. Rows moved by ALTER TABLE ... SWITCH never appear as inserts or deletes, so the destination drifts. Enabling a partitioned table with @allow_partition_switch = 0 blocks switches instead.
• Online index operations aren't supported on a CDC-enabled database (error 18773). Online ALTER TABLE DDL isn't supported either, on Azure SQL Managed Instance and on versions before SQL Server 2025.
• TRUNCATE TABLE isn't allowed on CDC-tracked tables. Purge jobs need DELETE instead.

Azure SQL Database and Amazon RDS

Azure SQL Database has no SQL Server Agent. A built-in scheduler runs capture every 20 seconds and cleanup every hour, and you can't change either frequency. In the DTU model, CDC needs S3 or higher (Basic and S0 to S2 aren't supported); any vCore tier works. A paused serverless database doesn't capture, and Microsoft gives no SLA for when changes land in the change tables. The Azure SQL Database CDC overview has the full list.

On Amazon RDS, the RDS system account owns the CDC jobs, so they don't show in SQL Server Management Studio. Snapshot restores and point-in-time restores disable CDC and remove its metadata, so you re-enable CDC and every table afterwards. Microsoft's known issues page covers the remaining error codes for SQL Server and Managed Instance.

Streaming SQL Server CDC into a warehouse

The change tables give you a correct, ordered record of changes inside SQL Server. Getting them into Snowflake, BigQuery or Databricks continuously means building the rest:

1. An initial snapshot with a clean handoff. Change tables only hold recent changes. Record sys.fn_cdc_get_max_lsn() before copying the existing rows, then apply changes from that LSN. Rows that change during the copy show up in both, so applying has to be idempotent.
2. A reader per capture instance running the checkpoint loop above, fast enough to stay inside retention.
3. An apply step that understands operations. Deletes (__$operation = 1) delete or soft-delete the downstream row, updates merge by key, and several changes to one key in a batch collapse to the last by __$start_lsn and __$seqval. Keeping every version downstream is a separate history table next to the replica.
4. Schema change handling. Detect DDL, rotate the capture instance, switch readers at the right LSN and add the column in the warehouse, which has its own rules (see Snowflake schema evolution).
5. Monitoring on capture latency, sys.dm_cdc_errors, log_reuse_wait_desc and reader lag against retention.

You can build that, or run a self-managed connector and own its operations; we compared the options in Debezium alternatives.

How Artie reads SQL Server

Artie streams SQL Server changes into Snowflake, BigQuery, Redshift, Databricks, ClickHouse, Iceberg and other destinations, with four capture methods to choose from:

• CDC with capture instances. Artie reads the change tables you enabled above with a service account that has SELECT on the database. We recommend a retention of 24 hours to 7 days, and rotating the capture instance as shown above when you alter a table.
• Change tracking, for when CDC can't be enabled. Artie reads the changed keys and operations, then queries the source tables for the row data.
• Active transaction log via SQL access, on SQL Server VMs and Azure SQL Managed Instance. Artie reads the active log directly. It needs FULL or BULK_LOGGED recovery, a sysadmin service account, primary keys and supplemental logging, which you get by enabling CDC on each table and then dropping the capture job so nothing accumulates in the cdc schema.
• Transaction log backups. Artie reads your .trn files instead of the live database, so latency follows your log-backup cadence.

The last two skip the change tables, which removes the capture job's work but not the truncation rule from the previous section: with CDC enabled and no capture job, the log still waits on REPLICATION. For the log-backup method, our docs include a scheduled sp_repldone job that marks transactions as distributed, and Artie warns when a database's log has been waiting on REPLICATION for over an hour. For the active-log method, talk to us before running sp_repldone, since Artie reads changes from the active log.

Downstream, Artie creates the destination tables, applies deletes, adds new columns once the capture instance includes them, and can keep a history table of every change. If you run one database per customer, it can also unify the same table across databases into a single destination table. Setup for all four methods starts from the SQL Server source overview.

FAQ