CDC and Change Tracking
Overview
When the Change Data Capture (CDC) capture job stops, nothing complains. Changes stop reaching the change tables, and the transaction log cannot be truncated past the part CDC has not read, so the first sign is often a full log drive.
The CDC and Change Tracking report reads both features for one database and shows:
- status cards in a CDC row and a Change Tracking row, each red, yellow or green,
- the reasons under the cards, worst first, with what to do next,
- a chart of the last 32 CDC log scan sessions: latency as bars, transactions as a line,
- one grid with a toolbar switch between Capture Instances, Change Tracking, Scan Sessions, Errors and Jobs.
Only the parts that are on are shown: the CDC cards, chart and views when CDC is enabled, the change tracking cards and view when change tracking is enabled. A database with neither opens on one Not in use state.
Where to find it
| Route | How |
|---|---|
| Database tree | Expand a database → Real Time → CDC and Change Tracking |
The page title reads CDC and Change Tracking for <database name>. The item is not shown for master, model, msdb and tempdb, where neither feature can be enabled, or on SQL Server 2005.
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads the database again |
| Capture Instances / Change Tracking / Scan Sessions / Errors / Jobs | Which grid to show. Only the views that apply are offered, and the switch is only shown when there is more than one. The choice is remembered. Switching does not read the database again |
| Cancel | Stops a read in progress |
Reading the page
The heading names what is on, for example Change Data Capture and change tracking in <database>:, followed by the overall state: healthy, needs a look, needs attention now or partly unreadable. The line under it gives when the page was read and the SQL Server version. With CDC on it adds that log scan sessions and CDC errors are kept in memory and start again when the instance restarts.
The CDC cards:
| Card | What it shows | Color |
|---|---|---|
| Change Data Capture | Enabled, with the capture instance count and tracked tables | Unknown when the capture instances cannot be read |
| Capture Job | Running, Stopped, Scheduled (not continuous), Missing or Log Reader | See below |
| Capture Latency | The latency of the latest log scan session, and when it ended | Yellow over 300 seconds. Blue No scans when none has run since the instance started |
| Errors (24 Hours) | CDC errors in the last 24 hours, and the time of the last one | Yellow when there are any |
| Log Reuse Wait | log_reuse_wait_desc, or the recovery model when the wait is not CDC |
See below |
| Cleanup | The retention from the cleanup job, and its last run and outcome | Yellow when there is no cleanup job, it is disabled, its last run failed, or a change table is behind |
The capture job is:
- Red when it is missing (no capture job for the database, or a job listed in
cdc_jobsthat is not in SQL Server Agent), when SQL Server Agent is not running, or when a continuous capture job is not running. - Yellow when it exists but is disabled in SQL Server Agent.
- Blue when it is a scheduled (not continuous) job that is not running now, or when the database is published for transactional replication. Then the Log Reader Agent fills the change tables and there is no capture job (Log Reader).
Log Reuse Wait is REPLICATION with no publication on the database when the log is waiting for the CDC capture. That is red (CDC is holding the log) when the capture job is down or latency is over 300 seconds, and yellow (CDC capture has not caught up) otherwise.
The Change Tracking cards:
| Card | What it shows | Color |
|---|---|---|
| Change Tracking | Enabled, with the tracked table count and the current version | Unknown when the tables cannot be read |
| Retention | How long changes are kept | Green |
| Auto Cleanup | On or Off | Yellow when off: the internal tables only grow |
| Tracking Tables | The size of the change_tracking internal tables, and the rows and size of sys.syscommittab |
Green |
Hover a card for its tip: the job name, or what the server said when a read was refused.
The findings under the cards give each red and yellow reason in plain words, then what could not be read, then notes. With change tracking on, the last note is a manual check: a client that last synced at a version lower than a table’s Min Valid Version has lost changes and must reinitialize. The server does not record client versions, so compare each client’s saved version with that column.
The chart (CDC only) draws the last 32 log scan sessions, oldest to newest. Bars are latency in seconds: red when the session had errors, amber over 300 seconds, blue otherwise. A dashed line marks 300 seconds once the scale reaches it. The line is transactions per session, on its own scale. Hover a bar for the session’s times, latency, transactions, commands, empty scans and errors.
Reading the grid
Capture Instances, one row per capture instance:
| Column | What it is |
|---|---|
| Source Table | The tracked table |
| Capture Instance | The capture instance name |
| Status | OK, Empty, Cleanup behind by <time> (orange) or Could not read (gray) |
| Change Rows | Rows in the change table |
| Change MB | Size of the change table |
| Oldest Change | When the oldest change still held was committed |
| Oldest Age | How old that change is |
| Retention | The CDC retention from the cleanup job |
| Newest Change | When the newest change was committed |
| Change Table | The change table in the cdc schema |
| Columns | Captured columns |
| Net Changes | Whether net changes are supported |
| Created | When the capture instance was created |
| Gating Role | The role that gates access to the changes |
| Unique Index | The unique index used for net changes |
Cleanup is behind when the oldest change is older than the retention by more than one day.
Change Tracking, one row per tracked table:
| Column | What it is |
|---|---|
| Table | The tracked table |
| Track Columns Updated | Whether column updates are tracked |
| Min Valid Version | The oldest version a client can sync from |
| Begin Version | The version when tracking started on the table |
| Cleanup Version | The version cleanup has reached (blank before the first cleanup) |
| Internal Table Rows | Rows in the table’s change_tracking internal table |
| Internal Table MB | Size of that internal table |
A gray last row, sys.syscommittab (database wide), shows the commit table all tracked tables share: one row per committed transaction while change tracking is on.
Scan Sessions, the last 32 from sys.dm_cdc_log_scan_sessions, newest first: Session, Start, End, Duration s, Latency s, Errors, Phase, Transactions, Empty Scans, Commands, Log Records and Last Commit. A row is red when the session had errors and orange when latency is over 300 seconds.
Errors, the latest 100 from sys.dm_cdc_errors, newest first: Time, Session, Phase, Error, Severity, State and Message.
Jobs, the capture and cleanup jobs from msdb.dbo.cdc_jobs: Job, Type, Exists, Enabled, Running, Last Run, Last Outcome (Failed, Succeeded, Canceled or Unknown), then Continuous, Polling s, Max Trans and Max Scans for the capture job, and Retention and Threshold for the cleanup job.
Every grid exports to CSV and Excel like every other grid.
Right-click actions
Nothing on this page runs anything against the database. Each script opens in a window to copy.
| Item | When | What it shows |
|---|---|---|
| Script Start Capture Job | CDC is on | EXEC sys.sp_cdc_start_job @job_type = N'capture'. SQL Server Agent must be running |
| Script CDC Configuration Check | CDC is on | sys.sp_cdc_help_change_data_capture, sys.sp_cdc_help_jobs, and the latest 32 log scan sessions and 100 CDC errors |
| Copy the findings as text | Always | The heading, every card and every finding |
| Copy Chart to Clipboard | Always | The cards, findings and chart as a picture |
On the Capture Instances and Change Tracking views the menu also offers the table pages for the row, such as Table Size, Table Space Breakdown and Table Use.
Permissions and versions
- The capture instances come from
cdc.change_tables. The cdc schema is protected, so a login that is not db_owner (or sysadmin) cannot list them; the page says so. - The jobs come from
msdb.dbo.cdc_jobs,sysjobs,sysjobserversandsysjobactivity. If msdb cannot be read, whether the capture job is running is not known and the page says so. sys.dm_cdc_log_scan_sessionsandsys.dm_cdc_errorsneed VIEW DATABASE STATE (VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later).- Whether SQL Server Agent itself is running is checked from
sys.dm_exec_sessions, which needs VIEW SERVER STATE. Without it the page notes that it could not check. - Change tracking is read from
sys.change_tracking_databases,sys.change_tracking_tablesandsys.internal_tables. - Each part that is refused is named on the page; the rest still shows.
- Needs SQL Server 2008 or later.