SQL Server Change Data Capture: Step-by-Step Setup Guide

SQL Server’s built-in Change Data Capture (CDC) feature records INSERT, UPDATE, and DELETE activity on your tables by reading the transaction log. Every change gets written to a set of system tables that mirror your source schema, giving you a queryable history of what changed, when, and what the before/after values looked like. This is the foundation for real-time data pipelines, audit trails, and event-driven architectures built on SQL Server.

This guide walks through the full setup process: prerequisites, enabling CDC at the database and table level, configuring the capture and cleanup jobs, monitoring with DMVs, fixing common problems, and streaming changes to external systems like Snowflake or BigQuery.

Prerequisites

Before enabling CDC, confirm these requirements:

SQL Server edition: CDC is available on Enterprise, Developer, and Standard editions (SQL Server 2016 SP1 and later for Standard). Azure SQL Database also supports CDC as of 2022. It is not available on Express edition.

SQL Server Agent: The capture and cleanup jobs run as SQL Server Agent jobs. Agent must be running and set to start automatically. On Azure SQL Database, Agent jobs are replaced by a built-in scheduler — no extra configuration needed.

Permissions: You need db_owner on the target database, or sysadmin at the instance level. The account running the capture job needs read access to the transaction log.

Disk space: CDC change tables store a copy of every changed row. Plan for additional storage proportional to your write volume. A table with 10,000 updates per day at 500 bytes per row generates roughly 5 MB of change data daily before cleanup runs.

Transaction log: CDC reads from the transaction log, so the log cannot be truncated past the point the capture job has processed. If the capture job stops or falls behind, the transaction log will grow until it catches up.

Step 1: Enable CDC on the Database

Connect to your SQL Server instance and run the following against your target database:

USE YourDatabase;
GO

EXEC sys.sp_cdc_enable_db;
GO

This creates the cdc schema in your database and sets up the infrastructure tables CDC needs. You can verify it worked:

SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'YourDatabase';

The is_cdc_enabled column should return 1.

Behind the scenes, this procedure creates the cdc.change_tables, cdc.captured_columns, cdc.index_columns, cdc.ddl_history, and cdc.lsn_time_mapping system tables. It also creates the cdc database role.

Step 2: Enable CDC on Individual Tables

CDC is enabled per table. For each table you want to track:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = N'cdc_reader',
    @capture_instance = N'dbo_Orders',
    @supports_net_changes = 1;
GO

Parameter breakdown:

• @source_schema and @source_name: The schema and table to track.
• @role_name: A database role that controls read access to the change table. Set to NULL if you want no role-based gating.
• @capture_instance: A name for this capture configuration. Defaults to schema_table if omitted. You can have up to two capture instances per table (useful during schema migrations).
• @supports_net_changes: Set to 1 to enable net change queries (the final state of each row across a range of LSNs). Requires a primary key or unique index on the source table.

Verify the table is enabled:

SELECT s.name AS schema_name, t.name AS table_name, t.is_tracked_by_cdc
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_tracked_by_cdc = 1;

Selecting Specific Columns

If you only need to track a subset of columns, pass a column list:

EXEC sys.sp_cdc_enable_table
    @source_schema  = N'dbo',
    @source_name    = N'Customers',
    @role_name      = NULL,
    @captured_column_list = N'CustomerID, Email, Status, UpdatedAt',
    @supports_net_changes = 1;
GO

This reduces the size of your change tables and limits what gets exposed downstream. You can always add a second capture instance later if you need additional columns.

Step 3: Configure the Capture Job

When you enable CDC on the first table, SQL Server automatically creates two Agent jobs:

1. cdc.YourDatabase_capture — reads new entries from the transaction log and writes them to the change tables.
2. cdc.YourDatabase_cleanup — removes old rows from the change tables.

The capture job runs continuously by default, polling the log every 5 seconds. You can tune its behavior:

EXEC sys.sp_cdc_change_job
    @job_type = N'capture',
    @pollinginterval = 2,      -- seconds between log scans
    @maxtrans = 1000,          -- max transactions per scan cycle
    @maxscans = 20,            -- max scan cycles per polling interval
    @continuous = 1;           -- 1 = run continuously, 0 = run once
GO

Tuning guidance:

• High-throughput OLTP systems: Lower @pollinginterval to 1-2 seconds and raise @maxtrans to 5000+. This keeps the capture job closer to the head of the log and prevents log growth.
• Low-write systems: The defaults (5-second polling, 500 max transactions) are fine. Reducing polling frequency saves a trivial amount of CPU.
• Batch load windows: If you run large bulk inserts overnight, temporarily increase @maxtrans so the capture job can keep pace.

After changing job parameters, restart the capture job:

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

Step 4: Tune the Cleanup Job

The cleanup job removes change table rows older than the retention period. The default retention is 3 days (4,320 minutes). If your downstream consumers need a longer replay window, extend it:

EXEC sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 10080;        -- 7 days in minutes
GO

The cleanup job runs once every 2 hours by default. On databases with heavy write activity, you may want to run it more frequently with a smaller batch size to avoid long-running delete operations:

EXEC sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 10080,
    @threshold = 10000;        -- max rows deleted per cleanup cycle
GO

Restart the cleanup job after changes:

EXEC sys.sp_cdc_stop_job @job_type = N'cleanup';
GO
EXEC sys.sp_cdc_start_job @job_type = N'cleanup';
GO

Warning: If you set retention too low, downstream consumers that poll less frequently than the retention window will miss changes. If you set it too high, change tables grow and consume storage. Match retention to the maximum lag your slowest consumer can tolerate, plus a safety buffer.

Step 5: Query Change Data

CDC provides two ways to read changes: all changes and net changes.

All Changes

Returns every individual operation (insert, update, delete) within an LSN range:

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Orders');
SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(
    @from_lsn, @to_lsn, N'all update old'
);

The __$operation column tells you the type:

• 1 = delete
• 2 = insert
• 3 = update (before image)
• 4 = update (after image)

Net Changes

Returns only the final state of each row across the LSN range — useful when you only care about the latest value:

SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(
    @from_lsn, @to_lsn, N'all'
);

Mapping LSNs to Timestamps

To convert LSN values to human-readable times:

SELECT sys.fn_cdc_map_lsn_to_time(@from_lsn) AS change_time;

Or query changes within a time range:

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2026-03-01 00:00:00');
SET @to_lsn   = sys.fn_cdc_map_time_to_lsn('largest less than or equal', '2026-03-01 23:59:59');

SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, N'all');

Step 6: Monitor CDC with DMVs

Ongoing monitoring keeps your CDC setup healthy. Here are the queries that matter.

Check Capture Job Latency

SELECT
    latency,
    command_count,
    duration
FROM sys.dm_cdc_log_scan_sessions
ORDER BY start_time DESC;

latency is the delay in seconds between when a transaction committed and when the capture job processed it. If this number is growing, the capture job is falling behind.

Check Transaction Log Usage

DBCC SQLPERF(LOGSPACE);

If your database’s log usage is climbing and stays high, the capture job may not be processing fast enough. The transaction log cannot truncate past the oldest unprocessed LSN.

View Active Capture Instances

SELECT *
FROM cdc.change_tables;

Check Change Table Sizes

SELECT
    OBJECT_NAME(object_id) AS change_table,
    SUM(row_count) AS row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_NAME(object_id) LIKE '%_CT'
    AND index_id IN (0, 1)
GROUP BY object_id
ORDER BY row_count DESC;

Large row counts in change tables mean either your retention window is long or the cleanup job is not running properly.

Common Errors and Fixes

”CDC is not enabled on this database”

You forgot to run sys.sp_cdc_enable_db, or it failed silently. Check sys.databases.is_cdc_enabled and re-run the enable procedure.

SQL Server Agent Is Not Running

CDC capture and cleanup are Agent jobs. If Agent is stopped, no changes get captured and the transaction log grows. On Windows, check the service:

EXEC xp_servicecontrol 'QueryState', 'SQLServerAgent';

Start it via SQL Server Configuration Manager or net start SQLSERVERAGENT from an elevated command prompt.

Transaction Log Full

This usually means the capture job is stopped or lagging. Check Agent job history for failures. If the log is already full and you need an immediate fix:

-- Temporary: back up the log to free space
BACKUP LOG YourDatabase TO DISK = 'C:\Backups\YourDatabase_log.trn';

Then restart the capture job and investigate why it stopped.

”Cannot enable CDC on a table that has more than one capture instance”

SQL Server allows a maximum of two capture instances per table. If you already have two, disable one before creating a new one:

EXEC sys.sp_cdc_disable_table
    @source_schema  = N'dbo',
    @source_name    = N'Orders',
    @capture_instance = N'dbo_Orders_old';
GO

Schema Changes on CDC-Enabled Tables

Adding or dropping columns on a tracked table does not automatically update the capture instance. You need to create a new capture instance with the updated column list, then drop the old one after consumers switch over:

-- Create new instance with the added column
EXEC sys.sp_cdc_enable_table
    @source_schema  = N'dbo',
    @source_name    = N'Orders',
    @capture_instance = N'dbo_Orders_v2',
    @role_name      = NULL,
    @supports_net_changes = 1;
GO

-- Once consumers are reading from v2, drop the old instance
EXEC sys.sp_cdc_disable_table
    @source_schema  = N'dbo',
    @source_name    = N'Orders',
    @capture_instance = N'dbo_Orders';
GO

Cleanup Job Fails with Timeout

If the change tables are very large, the cleanup job may time out trying to delete millions of rows at once. Lower the @threshold parameter to delete fewer rows per cycle and run cleanup more frequently.

CDC on Azure SQL Database

Azure SQL Database supports CDC without SQL Server Agent. The capture and cleanup processes are managed by a background scheduler. Enabling works the same way:

EXEC sys.sp_cdc_enable_db;
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL,
    @supports_net_changes = 1;
GO

The main differences on Azure SQL Database:

• No Agent jobs to manage. Capture runs automatically in the background.
• Retention defaults to 3 days and can be adjusted the same way.
• DTU/vCore usage increases slightly when CDC is active. On lower tiers, monitor your resource consumption.
• Azure SQL Managed Instance works identically to on-premises SQL Server, including Agent jobs.

Streaming CDC Changes to External Systems

Querying change tables directly works for simple use cases, but it becomes a maintenance headache when you need to stream changes to a data warehouse, a search index, or a message queue. You end up building custom polling logic, tracking LSN offsets, handling retries, and managing schema changes — all of which are undifferentiated infrastructure work.

This is where Change Data Capture as a managed service becomes valuable. Instead of writing and maintaining your own consumer, a CDC platform reads the SQL Server transaction log (or the change tables) and delivers events to your destination with exactly-once semantics, automatic schema evolution, and built-in monitoring.

What a Managed Pipeline Looks Like

1. Connect to SQL Server: Point the platform at your SQL Server instance. It reads the transaction log directly, so it captures changes with minimal latency and no polling overhead on the change tables.
2. Select tables: Choose which tables to replicate. The platform handles initial snapshots (backfilling existing data) and then switches to streaming mode for ongoing changes.
3. Configure the destination: Pick your target — Snowflake, BigQuery, Databricks, Elasticsearch, Kafka, or others. The platform maps SQL Server schemas to the destination format automatically.
4. Monitor and alert: Dashboards show replication lag, throughput, and errors. If a schema change breaks the pipeline, you get notified instead of discovering stale data days later.

The SQL Server connector in Streamkap follows this model. It connects to the transaction log, captures changes with sub-second latency, and delivers them to your destination without you managing Debezium clusters, Kafka infrastructure, or custom consumer code.

Why Not Build It Yourself?

Teams that build their own CDC consumers on top of SQL Server’s change tables typically run into these problems within the first year:

• LSN tracking state: You need durable, consistent offset storage so you do not replay or skip changes after a restart.
• Schema drift: When someone adds a column to a source table, your consumer and destination schema need to stay in sync. Miss one ALTER TABLE and your pipeline breaks silently.
• Backfill and re-sync: When you add a new table or need to rebuild a destination, you need snapshot logic that runs alongside ongoing CDC without duplicating or dropping events.
• Monitoring: You need alerting on capture lag, change table growth, and consumer health. These are custom metrics that do not exist out of the box.

A managed CDC platform handles all of this for you. The trade-off is cost vs. engineering time — and for most teams, the engineering time is more expensive.

Disabling CDC

To remove CDC from a table:

EXEC sys.sp_cdc_disable_table
    @source_schema  = N'dbo',
    @source_name    = N'Orders',
    @capture_instance = N'dbo_Orders';
GO

To disable CDC for the entire database (this removes all capture instances, change tables, and Agent jobs):

EXEC sys.sp_cdc_disable_db;
GO

Disabling drops the change tables permanently. If you need the historical data, back it up before disabling.

Quick Reference

Want to stream SQL Server changes without managing the infrastructure? Streamkap connects directly to SQL Server’s transaction log and delivers changes to your warehouse in real time. Start a free trial or see the SQL Server connector.