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;

This is a post on how to enable Change Data Capture (CDC) in SQL Server.

Enabling this on a database and table will allow the recording of activity when tables and rows have been modified. This is a feature available for us to configure within SQL Server and Azure SQL out of the box.

CDC uses the SQL Agent to record inserts, updates and deletes that happen on a table. Enabling this might be useful to help give a business insight on changes to Personal Identifiable Information (PII) over time for GDPR requirements. Or, another example could be real-time tracking of orders for pricing updates to a product.

For more examples and information on what CDC is, we can refer to MS Docs : What is change data capture (CDC)?

If you are considering enabling this feature, as always we should test and read through the software documentation to ensure all potential impacts and limitations have been reviewed. Some main considerations on the systems side with this one include the additional disk space your CDC tables will need, and performance depending on the number of tables/changes happening.

The demo below covers the following:
> Enabling Change Data Capture for a Database
> Enabling Change Data Capture for a Table

Enabling Change Data Capture for a Database

Before enabling CDC on a database we want to first check:
1. Is CDC already enabled.
Best to check this in larger or more unknown environments.
2. Is the database owner is set to something other than ‘sa’.
You’ll receive an error message if you don’t have it set to correctly.

We can check the above configured settings by querying sys.databases.

-- Check if Database is has CDC Enabled
SELECT is_cdc_enabled, *
FROM sys.databases;

-- (IF_REQUIRED) Change Database Owner to 'sa'
-- Error if <> 'sa' = "Could not update the metadata that indicates database demoBlog is enabled for Change Data Capture"
ALTER AUTHORIZATION ON DATABASE::[demoBlog] TO [sa]
GO

Enabling CDC for a SQL Server database is done executing the sys.sp_cdc_enable_db system stored procedure, as shown in this example:

USE [demoBlog];
GO
-- Enable CDC for Database ms sql
EXEC sys.sp_cdc_enable_db
GO
/* New system tables > 
	[cdc].[captured_columns];
	[cdc].[change_tables];
	[cdc].[ddl_history];
	[cdc].[index_columns];
	[cdc].[lsn_time_mapping];
	[dbo].[systranschemas];
*/

We can verify the database is enabled for CDC by checking sys.databases, and there’s new system tables for CDC as displayed in the screenshot above.

Enabling CDC for a Table

I’ve just enabled CDC on this demo database, so now it’s time to enable it on some tables.

We can check if CDC is already enabled by querying the sys.tables table and checking the is_tracked_by_cdc column.

To control the placement of change table data, we can create Filegroups ahead of enabling CDC on tables. Microsoft recommends we do this so that our change tables are separate from source tables.

I’m now creating a new File Group for CDC below:

-- Create File Group for CDC
ALTER DATABASE [demoBlog] ADD FILEGROUP [DemoBlog_CT];

ALTER DATABASE [demoBlog]
ADD FILE
(
    NAME = demoBlog_data1,
    FILENAME = 'D:\MSSQL_DATA\demoBlog_data1.ndf',
    SIZE = 500MB,
    FILEGROWTH = 50MB
) TO FILEGROUP [DemoBlog_CT];

SELECT * FROM sys.filegroups;
SELECT * FROM sys.database_files;

Use sys.sp_cdc_enable_table to enable CDC on a table, as per my next example below.

When a table is enabled for CDC, a record will be logged in the transaction log for each DML operation applied to the table from that point onwards.

USE [demoBlog];
GO 

-- Enable CDC on Table
EXEC sys.sp_cdc_enable_table
	@source_schema = N'dbo',
	@source_name   = N'posts',
	@role_name     = NULL,
	@filegroup_name = N'DemoBlog_CT'
GO
-- Job 'cdc.demoBlog_capture' started successfully.
-- Job 'cdc.demoBlog_cleanup' started successfully.

Upon executing the above SQL, new SQL Agent jobs are created for the CDC Collection.

One interesting note, “If both CDC and replication are enabled for a database, the log reader handles the population of the CDC change tables.”.

For an extended demo, here’s an example inserting a new of a row into our table, and then a querying the CDC metadata to watch it seed through:

I hope this post was useful and gave some good info! Feel free to add a comment below for any questions or suggested updates I can do here.