Disabling Change Data Capture in SQL Server

This post contains a demo on how to disable CDC (Change Data Capture) in SQL Server 2022. The sp_cdc_disable_table and sp_cdc_disable_db in-built MSSQL SPs are what we should use to disable CDC in SQL Server which is explained more below.

The sp_cdc_disable_table and sp_cdc_disable_db are the in-built MSSQL SPs to do this which are explained below.

Disabling CDC can be a common task for a SQL DBA as we need to do this each time a new column is created on the source table. We run sp_cdc_enable_table to add all columns in a table to CDC, but then when a new column is created we need to drop CDC on the table and re-enable it in SQL.

My previous blog post explains how to enable CDC in SQL Server and will show you how to enable CDC. This post is a guide for the CDC disable action.

Before proceeding with a change like this on a production SQL environment, we should read Microsoft Docs on Enabling & Disabling CDC, and test the change to get familiar enough with CDC.

How to Disable CDC SQL Server

Disabling CDC for a table in MSSQL will drop all existing captured change data. If you are intending on performing this on a production SQL Server, you should verify whether we need to back up & copy existing CDC data.

To disable Change Data Capture on a table in SQL Server, we should first check for existing CDC configuration information by running sp_cdc_help_change_data_capture. Using this info we can populate the parameters for sp_cdc_disable_table, particularly the capture_instance name.

USE [demoBlog];
GO

-- Check current 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'

If you are removing CDC from all tables permanently, we can disable CDC on the database too.

Run sp_cdc_disable_db to disable CDC on a database in MS SQL as per the example below.

USE [demoBlog];
GO

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

-- Check if CDC is enabled on databases
SELECT is_cdc_enabled,* FROM sys.databases;