SQL Server Portal

In my earlier two articles, I demonstrated how to enable and utilize the Change Data Capture (CDC) in any SQL Server database with few simple steps. However if you enable this feature for testing purpose in the test server, or sometimes you enable it on the wrong database / table  by mistake, so you need to disable it by following few steps as shown below. Before proceeding with the disability of CDC feature in the database, you must make sure that you DO NOT need the changes recorded by CDC because once you disable it, the CDC data will no longer be available.

Let me explain how to disable the CDC from any respective table & database in few easy steps.

Step 1 – Validating SQL Server Agent:
First of all you must make sure that your SQL Server Agent is UP and RUNNING as shown below.

Step 2 – Find capture instance name :
The next step is to find the name of the capture instance (audit table) of any particular table that you need to disable for CDC. In order to achieve it, you need to execute sys.sp_cdc_help_change_data_capture (system stored procedure) to get all the list of CDC enabled objects along with its capture instance name as shown below.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

Step 3 – Disable CDC for tables:
Once you have the list, you need to note the capture instance name and execute the given below script with schema, table & capture instance name.
This script will disable the CDC feature for the particular table and you will lose all the CDC data for the given below table.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO
--OUTPUT

Step 4 – Disable CDC for database:
In this step, we will disable the CDC feature from any database. Given below is the script that will NOT only disable CDC for any database but also will disable the CDC for all tables in that database. So be careful while executing this statement.

--This script is compatible with SQL Server 2008 and above.

USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_db
GO
--OUTPUT

Let me know if you enabled CDC in your SQL Server and its feedback.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.