SQL Server CDC: capture job lag and a transaction log that won't truncate (log_reuse_wait = REPLICATION)

#Symptom

• The transaction log of a CDC-enabled database grows steadily; log backups run but don't free space.
• log_reuse_wait_desc shows REPLICATION.
• Downstream consumers (SSIS, Debezium, ADF) see changes minutes or hours late.

#Why

The CDC capture job reads the transaction log (the same log reader used by transactional replication) and writes changes into change tables. A log record can't be truncated until CDC has processed it. If the capture job stops or falls behind, nothing behind its position can be reused, no matter how many log backups you take.

#Step 1: confirm CDC is the blocker

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

DBCC SQLPERF(LOGSPACE);

REPLICATION with is_cdc_enabled = 1 points to CDC (or to transactional replication, if the database is also a publisher).

#Step 2: is the capture job running?

USE SalesDB;
EXEC sys.sp_cdc_help_jobs;

Then check SQL Server Agent: job cdc.SalesDB_capture should be running (it runs continuously). Check that SQL Server Agent itself is running; after a failover or a service restart, Agent may be stopped.

SELECT j.name, ja.start_execution_date, ja.stop_execution_date
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = j.job_id
WHERE j.name LIKE 'cdc.%capture'
  AND ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions);

Start it if needed:

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

#Step 3: measure capture latency and errors

SELECT TOP (20) session_id, start_time, end_time, duration, tran_count,
       command_count, latency, empty_scan_count, error_count
FROM sys.dm_cdc_log_scan_sessions
ORDER BY session_id DESC;

SELECT TOP (20) * FROM sys.dm_cdc_errors ORDER BY entry_time DESC;

• latency (seconds): how far behind the log the capture is.
• error_count > 0: read sys.dm_cdc_errors; common causes are schema changes and permissions.
• Very long duration with high command_count: capture is working but can't keep up.

#Step 4: tune the capture job

Defaults: 500 transactions per scan (maxtrans), 10 scans per cycle (maxscans), 5 s polling interval.

EXEC sys.sp_cdc_change_job @job_type = N'capture',
     @maxtrans = 5000, @maxscans = 20, @pollinginterval = 1;

-- Changes take effect after the job restarts
EXEC sys.sp_cdc_stop_job  @job_type = N'capture';
EXEC sys.sp_cdc_start_job @job_type = N'capture';

Raise gradually and watch latency. Larger batches catch up faster but make each scan heavier.

#Step 5: large transactions and maintenance

A single huge transaction (bulk delete, index rebuild in the same log stream, a big ETL update on a CDC-tracked table) must be fully read before CDC moves on. During that time the log keeps growing.

• Split large DML on tracked tables into batches.
• Run index maintenance in a window and watch CDC latency during it.
• Avoid enabling CDC on tables that receive full reloads; CDC then captures every row twice (delete + insert), which is expensive.

#Step 6: cleanup job and change table size

The cleanup job removes change rows older than the retention (default 4320 minutes, 3 days). If it's disabled or failing, change tables grow, inserts into them slow down, and capture slows with them.

EXEC sys.sp_cdc_help_jobs;   -- check retention and threshold for the cleanup job
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 1440;  -- 1 day, if consumers keep up

Set retention longer than the longest consumer outage you want to survive.

#Availability Groups

• CDC jobs must exist on every replica, but run only on the primary. Create them on secondaries before a failover (sys.sp_cdc_add_job), and make sure the capture job checks whether the replica is primary.
• On AGs, CDC only reads log records that have been hardened on the secondaries. A slow secondary can also delay CDC and log truncation.

#Emergency: disk almost full

1. Start or fix the capture job (Steps 2-4). That is the real fix.
2. Add log space (extra log file on another drive) to buy time.

Caution

sp_repldone and disabling CDC (sys.sp_cdc_disable_db) will free the log, but they lose change data that consumers haven't read. Downstream systems then need a full reload. Use them only with the data owner's agreement.

#Prevent it next time

• Alert on log_reuse_wait_desc = 'REPLICATION' lasting more than 15 minutes, and on CDC latency.
• Monitor SQL Server Agent service state; a stopped Agent stops CDC.
• Related: transaction log full (9002), Debezium CDC lag.