SQL Server Change Data Capture, Explained and Configured SQL Server change data capture (CDC) records the inserts, updates, and deletes made to the tables you...
SQL Server change data capture (CDC) records the inserts, updates, and deletes made to the tables you choose. It keeps the before and after values of each changed row and stores them in change tables that downstream jobs can query. ETL and data warehouse teams use it to load only the rows that changed.
CDC runs on the transaction log and SQL Server Agent, so it stays healthy only while those components do. It also ships only in certain editions, so your edition decides whether you can use it at all.
Change data capture is a SQL Server feature that tracks row-level insert, update, and delete activity on the tables you select and returns each change as a queryable row. Microsoft’s CDC overview says the typical consumer is an ETL application that loads a data warehouse or data mart incrementally. Each run pulls only the rows that changed since the previous run and does not rescan whole source tables.
Microsoft’s SQL Server 2025 editions matrix lists change data capture in the Enterprise and Standard editions but not in Express, so check the edition before you plan anything else. Change tracking, a lighter feature compared later in this guide, is listed for all three editions.
You enable CDC at two levels, and each level needs its own permission. A member of the sysadmin fixed server role enables or disables CDC for a database. After that, members of db_owner can enable individual tables, according to Microsoft’s enable and disable reference. Each table you enable gets its own capture instance, which is the set of change table and query functions described in the next section.
When you enable a table, the optional @role_name parameter names a gating role. Only members of that role can read the table’s change data, and setting @role_name to NULL removes the restriction. Add the primary key before you enable a table, because CDC ignores any primary key added after the table was enabled without one.
Microsoft’s overview describes three stages that every change passes through. First, SQL Server writes an insert, update, or delete on a tracked table to the transaction log. Next, the capture process reads the log and writes the change into the change table for that source table. Finally, table-valued functions return the changes for a requested LSN range. Two SQL Server Agent jobs run this process. The capture job reads the log, and the cleanup job removes old change rows.
The capture process reads committed changes from the transaction log asynchronously, after the originating transaction has finished. The log is its only source, so the change table follows the same order of committed work that the database engine recorded. Every change row carries the log sequence number (LSN) of its commit, plus a sequence value that orders the operations inside a single transaction.
To read changes, a consumer requests a range between a low LSN and a high LSN. It then stores the high LSN it finished processing and starts its next request just past that point. PowerSync’s engineering write-up on building SQL Server replication notes that consumers have to poll the change tables by LSN range. PostgreSQL logical replication and the MySQL binlog work differently.
Each capture instance has a validity interval that limits which LSNs a consumer can request. Cleanup moves the low end of that interval forward as it deletes old rows. A consumer that falls further behind than the retention period will ask for LSNs whose change rows have already been removed.
Running sys.sp_cdc_enable_table on a table creates a capture instance. A capture instance is a change table plus up to two query functions, one that returns all changes and one that returns net changes. By default the change table lives in the cdc schema and is named <schema>_<table>_CT, so dbo.Orders gets cdc.dbo_Orders_CT. The change table mirrors the captured source columns and adds metadata columns, including __$start_lsn, __$seqval, __$operation, and __$update_mask.
SQL Server stores each capture instance’s definition in cdc.change_tables, the columns it tracks in cdc.captured_columns, and its index columns in cdc.index_columns. When a consumer reports a missing column, check these tables first, because they show what the capture instance was built to track.
Microsoft’s CDC overview says the capture job starts as soon as it is created and runs continuously. The cleanup job runs every day at 2 A.M. and keeps change table entries for 4320 minutes, which is 3 days, before it deletes them.
The capture job’s batch size depends on which Microsoft page you read. The overview says the job processes a maximum of 1000 transactions per scan cycle and waits 5 seconds between cycles. The sys.sp_cdc_add_job reference sets the @maxtrans default at 500 and the @maxscans default at 10. The Debezium SQL Server connector documentation also lists 500 for maxtrans, with a polling interval of 5 seconds, and adds that a lower maxtrans reduces load on the database host but increases latency.
Neither figure is safe to assume. Run the following in the CDC-enabled database to see the values configured on your instance.
EXEC sys.sp_cdc_help_jobs;
SELECT database_id, job_type, maxtrans, maxscans,
continuous, pollinginterval, retention, threshold
FROM msdb.dbo.cdc_jobs;
The retention column shows the cleanup window in minutes, and the capture row shows the batch settings the job uses.
You change these settings with sys.sp_cdc_change_job. Microsoft notes that the new values take effect only after the job is stopped and restarted.
EXEC sys.sp_cdc_change_job @job_type = N'capture', @maxtrans = 500;
EXEC sys.sp_cdc_stop_job @job_type = N'capture';
EXEC sys.sp_cdc_start_job @job_type = N'capture';
The log holds on to changes until capture collects them, and Microsoft confirms that stopping and starting the capture job does not lose change data.
Setup takes two calls to enable CDC, one query to confirm that changes are coming through, and a status check. The steps below follow Microsoft’s enable and disable reference and use a sample SalesDB database with a dbo.Orders table. Make sure SQL Server Agent is running before you start. The capture job is an Agent job, and it does nothing while Agent is stopped.
The first call turns on CDC for the database, and the second creates a capture instance for dbo.Orders.
USE SalesDB;
GO
EXEC sys.sp_cdc_enable_db;
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@role_name = N'cdc_reader';
GO
The database call requires sysadmin, and the table call requires db_owner. This example doesn’t pass @capture_instance, so SQL Server names the capture instance dbo_Orders and creates cdc.dbo_Orders_CT. Pass @role_name = NULL if you don’t want a gating role. Add @captured_column_list if you want to capture only some of the table’s columns.
The all-changes function takes a low LSN, a high LSN, and a row filter. This query returns everything currently held for dbo_Orders.
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 *
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, N'all');
The N’all’ filter returns one row per change, and updates come back with their after values. The N’all update old’ filter also returns the before image of each update. A production consumer saves @to_lsn after each run and uses sys.fn_cdc_increment_lsn on it to set the next run’s starting point.
Check the is_cdc_enabled column in sys.databases for the database, and the is_tracked_by_cdc column in sys.tables for individual tables. A value of 1 means CDC is on.
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = N'SalesDB';
SELECT SCHEMA_NAME(schema_id) AS schema_name, name, is_tracked_by_cdc
FROM SalesDB.sys.tables
WHERE is_tracked_by_cdc = 1;
EXEC sys.sp_cdc_help_change_data_capture;
The last procedure also shows the capture instance name, captured columns, and start LSN for each enabled table. A table can show as tracked while nothing reaches its change table, so run sys.sp_cdc_help_jobs as well and check the health views described later.
SQL Server has three native features for detecting data changes, and each gives a consumer something different. Change tracking reports which rows changed. CDC reports the values before and after each change. Change event streaming is new in SQL Server 2025, is still in preview, and pushes change events out to Azure messaging services. The table compares the three.
| Feature | Capture model | What the consumer receives | Editions listed for SQL Server 2025 |
|---|---|---|---|
| Change tracking | Synchronous, values discarded | Which rows changed, with only the key for a delete | Enterprise, Standard, Express |
| Change data capture | Asynchronous read of the transaction log | Before and after values, polled by LSN range | Enterprise, Standard |
| Change event streaming (preview) | DML changes streamed as CloudEvents in JSON or Avro Binary | Events delivered to Azure Event Hubs or Fabric Eventstream | Enterprise, Standard, Express, with PREVIEW_FEATURES enabled |
Pick the feature by what your consumers need (values, keys, or pushed events) and by whether your edition includes it.
Change tracking runs synchronously and records only that a row changed. CDC reads the log asynchronously and keeps the changed values. PowerSync built its SQL Server replication on CDC for this reason. With change tracking, a consumer has to run extra queries against the source table to get current values, and those queries can return inconsistent results. Change tracking also returns only the key for a deleted row and requires a primary key or unique constraint.
IDERA’s article on SQL Server change tracking limits explains the same difference from a DBA’s point of view. Change tracking costs less because it discards values, while CDC keeps them and pays for that in storage and processing. The article also describes a progression from change tracking to CDC to a dedicated audit trail.
Use change tracking when a consumer only needs to know which keys to resync and can read current values itself. Use CDC when the consumer needs the values from every change between syncs, such as a warehouse load that has to apply each update in order.
Change event streaming (CES) streams DML changes directly into Azure Event Hubs or Fabric Eventstream as CloudEvents, serialized as JSON or Avro Binary. According to Microsoft’s CES overview, it applies to SQL Server 2025 (17.x), Azure SQL Database, Azure SQL Managed Instance, and SQL database in Fabric. Microsoft says it is currently in preview and subject to change. The same page states that as of August 15, 2026, the AMQP protocol is deprecated for change event streaming.
CES is a separate feature that runs alongside CDC. The editions matrix lists it for Enterprise, Standard, and Express, and it requires the PREVIEW_FEATURES database-scoped configuration. PowerSync calls CES promising but warns that its “hard coupling to Azure Event Hubs adds an extra layer of complexity.” For production pipelines today, run CDC and evaluate CES while it remains in preview.
CDC suits any workload that needs an ordered record of row changes without rescanning source tables. Documented and published patterns include:
CDC does not follow changes to a table’s schema. Microsoft’s overview explains that the capture process ignores new columns that weren’t selected when the table was enabled. A dropped column that CDC was tracking shows up as NULL in later change rows. A data type change does carry through to the change table. You can read the DDL history for a capture instance with sys.sp_cdc_get_ddl_history.
To pick up a new column structure, create a second capture instance and run it alongside the first. Drop the old instance once the two timelines overlap. A source table can have no more than two capture instances at a time, so you have to drop the old one before you can repeat the process for the next schema change.
For dbo.Orders, steps two and four look like this.
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@role_name = N'cdc_reader',
@capture_instance = N'dbo_Orders_v2';
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@capture_instance = N'dbo_Orders';
Afterward, run sys.sp_cdc_help_change_data_capture to confirm that dbo_Orders_v2 is the only capture instance left on the table.
When the capture job stops or cleanup falls behind, queries against the source tables keep working normally, so CDC problems tend to show up as disk and log growth well before anyone notices missing change rows. Each of these problems has a documented cause and a way to detect it.
Microsoft states that in a CDC-enabled database, the log truncation point does not advance until the capture process has collected every change marked for capture. That rule holds under every recovery model, including the one DBAs choose so that checkpoints keep the log small. If the capture process is not running, a CHECKPOINT will not truncate the log.
A stopped SQL Server Agent or a failed capture job therefore lets the log keep growing until capture resumes. The Debezium documentation describes a symptom further downstream. When SQL Server Agent is not running, no maximum LSN is recorded, and the connector cannot start streaming.
According to Microsoft, change tables grow unmanageably if cleanup does not run. The cleanup job enforces retention by moving the low end of each capture instance’s validity interval forward and deleting the rows that fall below it. If that job is disabled or failing, every change row since enablement stays in the change tables.
Retention settings trade consumer recovery time against space. The 4320-minute default gives a downstream consumer 3 days to recover from an outage before the rows it needs are purged. A longer retention setting that covers an extended maintenance window protects the consumer, but it also grows every change table in the database, and that growth takes up space in the database’s data files.
Microsoft’s monitoring guidance names two dynamic management views for CDC health. sys.dm_cdc_log_scan_sessions returns one row per log scan session, which is one execution of sp_cdc_scan. Consecutive empty scans are combined into one row and counted in empty_scan_count. You measure capture latency and throughput from this view.
This query lists the most recent scan sessions.
SELECT TOP (20)
session_id, start_time, end_time, duration,
tran_count, command_count, latency, empty_scan_count
FROM sys.dm_cdc_log_scan_sessions
ORDER BY start_time DESC;
If latency keeps rising across recent sessions, capture is falling behind the workload. A high empty_scan_count is normal on a quiet database. On a database with steady writes to tracked tables, the same pattern points to an idle or lagging capture job and is worth investigating. If no new sessions appear at all, the capture job is not scanning.
Errors from the capture process are recorded in sys.dm_cdc_errors.
SELECT session_id, phase_number, entry_time,
error_number, error_severity, error_message
FROM sys.dm_cdc_errors
ORDER BY entry_time DESC;
For trend analysis, Microsoft suggests using the data collector to take regular snapshots of both views.
Each of these problems first appears as an instance-level symptom. Examples include a transaction log that keeps growing despite checkpoints, or data files that grow faster than the workload explains. Checking DMVs by hand on every instance that runs CDC doesn’t scale well. SQL Diagnostic Manager is built for this kind of instance-wide monitoring.
IDERA has offered SQL Diagnostic Manager for SQL Server monitoring for more than 15 years. It monitors SQL Server instances in real time from a central console, sends alerts based on thresholds you set, and keeps historical data for trend analysis and capacity planning. SQL Diagnostic Manager supports SQL Server 2012 through 2022, Azure SQL Database, Azure SQL Managed Instance, and Amazon RDS for SQL Server.
A DBA running CDC can set thresholds for the log and disk growth that a stalled capture job or lagging cleanup causes. The trend history also helps size log and data files for the retention window you chose.
CDC also leaves an audit gap. It captures what changed, with before and after values, but has no documented way to record who accessed data. Teams that enabled CDC to satisfy HIPAA, PCI, or SOX auditors need a tool built for that purpose. SQL Compliance Manager tracks DDL and DML changes across databases, monitors who accessed which data and when, and records permission changes and security events. It also includes pre-built reports for HIPAA, SOX, PCI, and GDPR.
CDC stays reliable only while its capture job runs, its cleanup job keeps up with the retention setting, and its capture instances match the table schema. When any of these slips, the transaction log and change tables grow until the problem becomes a capacity issue. SQL Diagnostic Manager gives DBAs real-time monitoring, custom alert thresholds, and trend history across their SQL Server instances. That way, log and disk growth from a stalled capture job is visible while there is still time to act.
Get Started with a 14-Day Free Trial of SQL Diagnostic Manager and set alert thresholds on the instances where CDC runs. If you enabled CDC to meet an audit requirement, start a SQL Compliance Manager trial to cover the access and security auditing that CDC does not record.
Run sys.sp_cdc_enable_db to enable CDC on the database, which requires sysadmin. Then run sys.sp_cdc_enable_table for each table, which requires db_owner, and make sure SQL Server Agent is running so the capture job can read the log. To retrieve changes, query cdc.fn_cdc_get_all_changes_ with an LSN range. Save the last LSN you processed so the next run starts where the previous one ended.
Change data capture means recording each insert, update, and delete so that another system can consume only the changes and doesn’t have to reload full tables. In SQL Server, the feature reads those changes from the transaction log and writes them, with before and after values, to change tables.
Query is_cdc_enabled in sys.databases for the database and is_tracked_by_cdc in sys.tables for each table. A value of 1 means CDC is enabled. Also run sys.sp_cdc_help_jobs, because an enabled table receives change rows only while the capture job is running.
Change tracking is synchronous and records only that a row changed. It returns just the key for a delete, so consumers have to query the source table for current values. CDC reads the transaction log asynchronously and keeps before and after values, which costs more storage and processing. CDC is available in Enterprise and Standard, while the SQL Server 2025 editions matrix also lists change tracking for Express.
A DML trigger runs as part of the statement that fires it. Any change logging the trigger performs adds work inside the user’s transaction, and you have to write and maintain that logging code yourself. CDC reads committed changes from the transaction log through a separate Agent job and provides standard change tables and query functions. Triggers are still an option on Express, which doesn’t include CDC.
System-versioned temporal tables keep the full history of each row in a history table with validity periods, so you can query a table as it looked at a past point in time. CDC is built to feed downstream consumers, with changes ordered by LSN and a default retention of 3 days. Temporal tables suit long-lived row history inside the database. CDC suits cases where another system needs to consume changes incrementally.
Microsoft’s SQL Server 2025 editions matrix lists change data capture in the Enterprise and Standard editions but not in Express. Change tracking and the preview change event streaming feature are listed for all three editions. Change event streaming also requires the PREVIEW_FEATURES database-scoped configuration.
Contact our sales team to get a personalized demo of our database management software for SQL Server!
