CDC keeps a useful history only while its jobs keep moving. When capture stops or CDC cleanup falls behind, the log and change tables become the first places to look.
Separate Capture From CDC Cleanup
The capture job reads the transaction log and writes change tables. The cleanup job removes change rows older than the retention setting. A stopped capture job can leave changes waiting in the log and contribute to log reuse being held. A stopped cleanup job lets change tables grow. I check both jobs before changing retention. Shortening retention will not restart a broken capture job, and restarting capture will not remove months of old rows if cleanup is misconfigured.
Confirm that CDC is enabled for the database and the expected tables. Ask consumers how far behind they can be before rows are deleted. The right retention value follows consumer recovery needs, not a guess about disk space.
Check Log Reuse and Job State
sys.databases.log_reuse_wait_desc shows why log truncation is waiting. CDC activity can surface through a REPLICATION reuse wait. That label does not tell you which capture job failed; inspect job state and history too. Run sys.sp_cdc_help_jobs in the CDC-enabled database to see capture and cleanup configuration. I note the last successful run, current status, and error text. A job can be enabled yet failing every run.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE is_cdc_enabled = 1;
EXEC sys.sp_cdc_help_jobs;Set CDC Cleanup Retention to the Consumer Contract
The cleanup job's retention is measured in minutes. The default is 4320 minutes, which is three days, and the example below raises it to seven days. Choose a value long enough for routine delays, maintenance, and consumer recovery. A very short value can delete changes before a downstream process catches up. A very long value increases storage and cleanup work. Change the job with sys.sp_cdc_change_job, then stop and restart the relevant CDC job for settings that require a restart. Read the current documented behavior for the installed version. Do the adjustment in a maintenance window and verify the new setting afterward.
EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 10080;
EXEC sys.sp_cdc_change_job
@job_type = N'capture',
@pollinginterval = 10;
EXEC sys.sp_cdc_help_jobs;Tune Capture Polling Deliberately
Capture polling sets the number of seconds between log scan cycles during continuous operation. Lower latency can increase wakeups and work. Higher latency can leave consumers waiting longer. Check current @pollinginterval, @maxtrans, and @maxscans through the job metadata before changing them. I adjust one setting at a time and watch change lag, log reuse, and CPU. A fast polling setting cannot overcome a capture job that is blocked by another issue. Read its Agent history and error log first.
Do not treat a retention change as a fix for full transaction log. Confirm log backups are running under full recovery and check other reuse waits. CDC is one possible cause, not the answer to every log-growth alert.
Alert on a Stopped Capture Job
Create a small monitoring check that compares the expected CDC jobs with Agent job status and recent success. A job not currently executing can be normal between schedules, so alert on missed successful runs or repeated failure, not merely an idle state. Include the database and job type in the message. I also watch capture lag and change table growth where the downstream system needs tight freshness. A green Agent status with a growing backlog deserves attention. A job that has never run returns NULL in the history columns, and that deserves an alert too.
SELECT j.name, j.enabled, h.run_date, h.run_time, h.run_status
FROM msdb.dbo.sysjobs AS j
OUTER APPLY
(
SELECT TOP (1) run_date, run_time, run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = j.job_id AND step_id = 0
ORDER BY instance_id DESC
) AS h
WHERE j.name LIKE N'cdc.%'
ORDER BY j.name;Watch the Log Before It Fills
When capture lags, compare the current log size, free space, log backup status, and log_reuse_wait_desc over time. A REPLICATION wait can coincide with other reasons the log grows. I review log backup history and the capture job's error first. Do not shrink the log repeatedly while the reuse problem remains; it will grow again and add more file-growth work. Pre-size it for the expected workload and give the team an alert threshold before free space becomes critical.
Check long transactions too. A long-running transaction can hold log truncation independently of CDC. I put the capture state, oldest active transaction, and log backup chain on the same timeline. The answer is in their sequence: which stopped first, which grew next, and what changed when capture resumed. A single current log_reuse_wait_desc is a snapshot, not a full incident history.
Keep Consumers From Falling Behind
Retention should exceed the longest tolerated outage of downstream consumers, including weekends and maintenance periods. Track each consumer's last processed LSN or bookmark in its own system. Compare it with the CDC minimum LSN before cleanup can remove required rows. I ask the consumer owner to prove restart from a saved bookmark in a test environment. A generous retention value only helps if someone notices a stopped consumer before the window closes.
What if a consumer falls beyond retention? Define a reinitialization path from a fresh snapshot and a clear point where incremental replay can resume. Do not quietly skip missing changes. I document that path beside the cleanup setting. CDC is useful because it lets consumers move incrementally; the operational cost is knowing when that promise is no longer possible. Capture, cleanup, and consumer progress all need monitoring.
Include CDC job health in the same alert path as ordinary SQL Agent failures. A capture job can be disabled during maintenance and forgotten. I record the expected enabled state and next successful run for each CDC database. A review after patching or failover should compare that expectation with actual Agent history before downstream consumers report stale data.
Verify Capture and CDC Cleanup After a Change
After restoring capture, verify new changes reach the change table and log reuse returns to its normal reason. After cleanup runs, check that old rows are removed and required consumer bookmarks remain available. Do not delete change rows by hand to make space. That can break consumers and obscure the underlying failure. What is the oldest unprocessed LSN for each consumer? Keep that answer near the retention setting.
I document the two jobs, their schedules, retention, consumer owner, and alert route. CDC looks automatic when it works. The runbook is what makes it recoverable when it stops.
Related reading on this blog: Error 9002: The Transaction Log for Database 'SQLAuthority' is Full Due to 'REPLICATION' and Transaction Logs: The Good, The Bad, and The Ugly.
CDC retention is not a set-and-forget number, it is an operating promise to every consumer.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
