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
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;