Disable Change Data Capture in SQL Server

July 30, 2026

–

Disabling Change Data Capture (CDC) is straightforward, but it has to happen in the right order: every tracked table first, then the database. Skip a step and SQL Server tells you plainly rather than doing it anyway, which is the safe behaviour but confusing the first time you hit it.

This post covers disabling CDC cleanly, what actually gets removed, and the one error message everyone hits on their first attempt.

When You’d Disable CDC

Decommissioning a database or retiring a downstream consumer that no longer needs change data
CDC was enabled for testing and never actually needed in production
Reworking table or filegroup design and disabling first is cleaner than migrating CDC alongside it
The capture or cleanup job has been failing for a while and the underlying need for CDC no longer exists

Check current status first if you’re not sure what’s actually enabled: Get CDC and Change Tracking reports every database with either feature configured in one pass.

Step 1: Disable CDC on Each Table

CDC must be disabled per table before the database itself can be disabled. Check what’s currently tracked first:

-- List every table currently tracked by CDC in the current database
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    ct.capture_instance
FROM cdc.change_tables AS ct
INNER JOIN sys.tables AS t ON ct.source_object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id;

Then disable CDC for each one:

-- Disable CDC for a specific table
USE [demoBlog];
GO

EXEC sys.sp_cdc_disable_table
    @source_schema      = N'dbo',
    @source_name        = N'posts',
    @capture_instance    = N'all';
GO

@capture_instance = N'all' removes every capture instance for that table. A table can have up to two capture instances at once (used when reconfiguring CDC without a gap in coverage), specify the exact instance name instead of 'all' if only one needs removing.

Microsoft Learn: sys.sp_cdc_disable_table

Step 2: Disable CDC on the Database

Once every table is clear, disable CDC at the database level:

-- Disable CDC for the whole database
USE [demoBlog];
GO

EXEC sys.sp_cdc_disable_db;
GO

This drops the cdc schema, every remaining change table, the two CDC SQL Agent jobs (cdc.<database>_capture and cdc.<database>_cleanup), and the CDC metadata tables in msdb for this database. It’s a clean removal, not a soft disable, there’s no “re-enable and pick up where you left off,” CDC starts fresh if it’s turned back on later.

The Error Everyone Hits First

Running sp_cdc_disable_db while tables are still tracked fails with a clear message rather than silently cleaning up on your behalf:

Msg 22902, Level 16, State 1
Could not update the metadata that indicates database [demoBlog] is not enabled for
Change Data Capture. The failure occurred when executing the command
'sys.sp_cdcdisabledb_internal'.

This means at least one table still has an active capture instance. Re-run the table-listing query from Step 1, there’s always at least one row left when this fires.

Microsoft Learn: sys.sp_cdc_disable_db

Verifying It’s Actually Off

-- Confirm CDC is disabled at the database level
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'demoBlog';

is_cdc_enabled = 0 confirms it. The cdc schema and its tables will also be gone entirely, querying cdc.change_tables after a successful disable returns an invalid object name, not an empty result, since the schema itself no longer exists.

Before You Disable in Production

Confirm nothing downstream is still consuming change data from this table or database first, disabling is immediate and the historical change data goes with it
If only some tables need CDC removed, disable those specifically rather than disabling the whole database and re-enabling the rest
There’s no export step built in, if the historical captured changes have any value, pull what’s needed from the change tables before disabling, not after

Blog Categories

Comments

Leave a Reply Cancel reply