Menu & Search
Disabling Change Data Capture (CDC) in SQL Server

Disabling Change Data Capture (CDC) in SQL Server

This post is a note on how to disable Change Data Capture (CDC) in SQL Server and follows on from yesterday’s post: How to Enable Change Data Capture (CDC) in SQL Server

Disabling CDC is required more often than you may think, as we need to do this to get new columns added to CDC. The default option when running sp_cdc_enable_table is for all columns to be added. But when a new column gets created afterwards, that’s when we need to drop/re-enable CDC.

MS Docs on Enabling/Disabling CDC can be found here. Have a read-through, test & understand you’re losing CDC data by doing this if in Production.

Disable CDC SQL Server

First, we’ll check the current CDC configuration data using sp_cdc_disable_table. Using the information we can populate the following sp_cdc_disable_table statement and execute:

USE [demoBlog];
GO

-- Check CDC config
EXEC sys.sp_cdc_help_change_data_capture 

-- Disable CDC for a table
EXECUTE sys.sp_cdc_disable_table
	@source_schema = N'dbo',
	@source_name = N'posts',
	@capture_instance = N'dbo_posts'
CDC Disable Table

If you’re completely removing CDC from all tables, once done we can disable CDC for the database:

USE [demoBlog];
GO

-- Disable CDC for a database
EXEC sys.sp_cdc_disable_db
GO

-- Verify
SELECT is_cdc_enabled,* FROM sys.databases;
sp_cdc_disable_db
Share

1 Comment

  1. […] next post covers Disabling CDC, which may be useful if you receive errors along the […]