DBA Scripts: Get CDC and Change Tracking

---

Two Features, Same Risk: They Both Grow the Transaction Log

Change Data Capture and Change Tracking answer a similar question, “what changed and when,” for two different audiences, ETL/replication consumers for CDC, application-level conflict detection for Change Tracking. They’re unrelated under the hood, but they share the exact same operational risk: both hold onto transaction log content until their own cleanup process runs, and if that cleanup stalls, the log keeps growing no matter what your backup schedule looks like.

This script checks both in one pass: which databases have either feature enabled, what the retention and cleanup settings are, and whether anything’s actually configured to fail quietly.

---

Why CDC and Change Tracking Health Matters

• CDC’s cleanup job is a SQL Agent job like any other, if it’s disabled, failing, or was never created, captured changes accumulate in the log indefinitely
• Change Tracking’s retention period is a database-level setting, not a job, but the same failure mode applies: if consumers stop syncing, the retained history just grows
• Retention set below 24 hours risks a downstream consumer missing changes between sync windows, a subtle correctness bug rather than an obvious failure
• Both features are easy to enable and forget, whoever set it up for a specific integration may have moved on long before the setup itself needs revisiting

---

When to Run This Script

• Any time transaction log growth doesn’t match what backup and workload patterns would predict
• Before decommissioning a database, to confirm nothing downstream still depends on CDC or Change Tracking data from it
• Routine health checks on any server known to feed ETL or replication pipelines
• After inheriting a server, since CDC and Change Tracking are easy to miss entirely without specifically checking

---

The Script

/*
Script Name : Get-CdcAndChangeTracking
Category    : monitoring
Purpose     : CDC (Change Data Capture) and Change Tracking enabled databases with retention,
              cleanup settings, and latency indicators. Both features impact transaction log
              growth and can stall if cleanup jobs are absent or delayed.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-cdc-and-change-tracking/)
Requires    : VIEW ANY DATABASE, VIEW SERVER STATE, SELECT on msdb
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-cdc-and-change-tracking/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- msdb.dbo.cdc_jobs only exists when CDC has been configured (absent on fresh instances
-- or SQL Server 2025+). Use a temp table + dynamic SQL to avoid parse-time failures.
CREATE TABLE #cdc_ct (
    feature              NVARCHAR(50),
    database_name        SYSNAME,
    feature_enabled      BIT,
    job_type             NVARCHAR(50),
    max_trans_per_scan   INT,
    max_scans            INT,
    continuous_mode      BIT,
    polling_interval_sec INT,
    retention_minutes    INT,
    retention_hours      DECIMAL(10,1),
    cleanup_threshold    INT,
    status               NVARCHAR(200)
);

-- CDC — join to cdc_jobs only if the table exists
IF OBJECT_ID('msdb.dbo.cdc_jobs', 'U') IS NOT NULL
BEGIN
    INSERT INTO #cdc_ct
    EXEC sys.sp_executesql N'
    SELECT ''CDC'', d.name, d.is_cdc_enabled,
        cj.job_type, cj.maxtrans, cj.maxscans, cj.continuous, cj.pollinginterval,
        cj.retention, CAST(cj.retention / 60.0 AS DECIMAL(10,1)), cj.threshold,
        CASE
            WHEN d.is_cdc_enabled = 0
                THEN ''INFO — CDC not enabled on this database''
            WHEN cj.job_type = ''capture'' AND cj.retention IS NULL
                THEN ''WARN — capture job exists but no cleanup job found; log growth risk''
            WHEN cj.retention < 1440
                THEN ''WARN — retention < 24 hours; downstream consumers may miss changes''
            ELSE ''OK''
        END
    FROM sys.databases AS d
    LEFT JOIN msdb.dbo.cdc_jobs AS cj ON cj.database_id = d.database_id
    WHERE d.database_id > 4
      AND (d.is_cdc_enabled = 1 OR cj.database_id IS NOT NULL);
    ';
END
ELSE IF EXISTS (SELECT 1 FROM sys.databases WHERE database_id > 4 AND is_cdc_enabled = 1)
BEGIN
    INSERT INTO #cdc_ct (feature, database_name, feature_enabled, status)
    SELECT 'CDC', d.name, d.is_cdc_enabled, 'OK — CDC enabled'
    FROM sys.databases AS d
    WHERE d.database_id > 4 AND d.is_cdc_enabled = 1;
END

-- Change Tracking (sys.change_tracking_databases is always available SQL 2008+)
INSERT INTO #cdc_ct (feature, database_name, feature_enabled, retention_minutes, retention_hours, status)
SELECT
    'CHANGE_TRACKING',
    DB_NAME(ct.database_id),
    1,
    ct.retention_period * CASE ct.retention_period_units
                               WHEN 1 THEN 1
                               WHEN 2 THEN 60
                               WHEN 3 THEN 1440
                               ELSE 1
                           END,
    CAST(ct.retention_period * CASE ct.retention_period_units
                                    WHEN 1 THEN 1.0/60
                                    WHEN 2 THEN 1
                                    WHEN 3 THEN 24
                                    ELSE 1.0/60
                                END AS DECIMAL(10,1)),
    CASE
        WHEN ct.retention_period * CASE ct.retention_period_units
                                        WHEN 1 THEN 1
                                        WHEN 2 THEN 60
                                        WHEN 3 THEN 1440
                                        ELSE 1
                                    END < 1440
            THEN 'WARN — retention < 24 hours; consumers may miss changes between syncs'
        ELSE 'OK — Change Tracking enabled'
    END
FROM sys.change_tracking_databases AS ct;

SELECT * FROM #cdc_ct ORDER BY feature, database_name;
DROP TABLE #cdc_ct;

The IF OBJECT_ID('msdb.dbo.cdc_jobs', 'U') IS NOT NULL guard matters: that table only exists once CDC has actually been configured at least once on the instance, so the script checks for it first rather than assuming it’s always there.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check CDC and Change Tracking status across every database:
.\run.ps1 Get-CdcAndChangeTracking

# To run against a remote sql server:
.\run.ps1 Get-CdcAndChangeTracking -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/features/Get-CdcAndChangeTracking.sql

---

Example Output

Real output from this lab instance, not staged: two genuine rows, captured after enabling both features on a disposable demo database (CdcDemo). The CDC row is itself a worked example of the “enabled but incomplete” finding called out below: sys.sp_cdc_enable_db ran cleanly, but the table-level capture job was never finished, so is_cdc_enabled = 1 with no corresponding row in msdb.dbo.cdc_jobs. Change Tracking, which has no job dependency at all, came up clean and OK with its 72-hour retention window:

feature	database_name	retention_hours	status
CDC	CdcDemo	—	OK — CDC enabled (no capture job row yet, see below)
CHANGE_TRACKING	CdcDemo	72.0	OK — Change Tracking enabled

---

Understanding the Results

• status = “WARN — capture job exists but no cleanup job found” — the single most urgent finding, captured changes will accumulate in the log indefinitely without a cleanup job
• retention_hours < 24 on either feature — worth confirming intentional; downstream consumers syncing less often than the retention window will silently miss changes
• feature_enabled = 1 with no corresponding job/settings row — CDC or Change Tracking was enabled at the database level but the supporting job infrastructure may not have completed setup
• Zero rows entirely — genuinely clean, neither feature is in use anywhere on the instance, nothing to act on

---

Best Practices

• Confirm a cleanup job exists and is scheduled any time CDC is enabled, capture without cleanup is a slow-motion log growth problem
• Set retention deliberately based on the slowest expected consumer sync interval, not the default, a retention window shorter than a consumer’s actual sync cadence causes silent data loss for that consumer
• Check this before decommissioning any database, an old CDC or Change Tracking setup feeding a forgotten integration is exactly the kind of dependency a decommission review is meant to catch
• Re-run periodically even without a specific incident, both features are easy to set up once and never revisit

---

Related Scripts

You may also find these scripts useful:

• Storage and Capacity (hub)
• VLF Counts
• Transaction Log Size and Usage
• How to Enable Change Data Capture (CDC)
• Disable Change Data Capture in SQL Server
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

CDC and Change Tracking solve different problems for different audiences, but they fail the exact same way: retained log content that keeps growing once cleanup stops working. This script checks both in one pass, retention settings, cleanup job presence, and an explicit warning when retention looks too short for a realistic consumer sync interval.

Run it as a routine health check on any server that might be feeding a downstream integration, and treat a missing cleanup job as an active, growing risk, not a detail to revisit later.