What’s the issue?
Change Data Capture (CDC) is a SQL Server feature that records insert, update, and delete activity on tracked tables into change tables, allowing downstream systems to consume a stream of data changes. CDC is typically enabled to support data warehousing, ETL processes, or replication to external systems such as Kafka or other analytics platforms.
This finding flags databases where CDC is currently enabled, which warrants review to confirm it is still needed and properly managed.
Why is this a problem?
CDC is not inherently bad, but it introduces overhead and operational complexity that is often forgotten after the original use case has ended. The CDC capture process reads the transaction log, which means the log cannot be truncated past the oldest unprocessed change, potentially causing the log file to grow unexpectedly if the capture job falls behind or is disabled.
Change tables consume storage that grows continuously unless the cleanup job runs properly, and the capture and cleanup jobs themselves add background workload. CDC also complicates certain operations including schema changes, database restores, and Always On failovers, since CDC metadata and jobs must be handled carefully. When CDC is left enabled after the consuming process has been decommissioned, it silently accumulates cost with no benefit.
What should you do about this?
Identify enabled databases by querying sys.databases where is_cdc_enabled = 1, and list the tracked tables with sys.tables filtered by is_tracked_by_cdc = 1 within each database. Confirm with application owners and the data or analytics teams whether CDC is still actively consumed, and by what. If the downstream consumer no longer exists, disable CDC on the affected tables using sys.sp_cdc_disable_table, then disable it at the database level with sys.sp_cdc_disable_db.
If CDC is still in use, verify that the capture and cleanup jobs are running successfully, monitor log file growth and change table size, and confirm the retention period is appropriate for your workload. Document which databases and tables use CDC, who the consumer is, and under what conditions it can be retired, so the same review does not have to be rediscovered later.
Read more…
What is change data capture (CDC)? – SQL Server | Microsoft Learn