Disabling Change Data Capture

This topic describes how to disable change data capture for a database and a table.

Disabling Change Data Capture for a Database

A member of the sysadmin fixed server role can run the stored procedure sys.sp_cdc_disable_db (Transact-SQL) in the database context to disable change data capture for a database. It is not necessary to disable individual tables before you disable the database. Disabling the database removes all associated change data capture metadata, including the cdc user and schema and the change data capture jobs. However, any gating roles created by change data capture will not be removed automatically and must be explicitly deleted. To determine if a database is enabled, query the is_cdc_enabled column in the sys.databases catalog view.

If a change data capture enabled database is dropped, change data capture jobs are automatically removed.

See the Disable Database for Change Data Capture template for an example of disabling a database.

Important

To locate the templates in SQL Server Management Studio, go to View, click Template Explorer, and then click SQL Server Templates. Change Data Capture is a sub-folder where you will find all the templates that are referenced in this topic. There is also a Template Explorer icon on the SQL Server Management Studio toolbar.

-- =================================

-- Disable Database for Change Data Capture template

-- =================================

USE MyDB
GO

EXEC sys.sp_cdc_disable_db
GO

Disabling Change Data Capture for a Table

Members of the db_owner fixed database role can remove a capture instance for individual source tables by using the stored procedure sys.sp_cdc_disable_table. To determine whether a source table is currently enabled for change data capture, examine the is_tracked_by_cdc column in the sys.tables catalog view. If there are no tables enabled for the database after the disabling takes place, the change data capture jobs are also removed.

If a change data capture-enabled table is dropped, change data capture metadata that is associated with the table is automatically removed.

See the Disable a Capture Instance for a Table template for an example of disabling a table.

-- ===============================================

-- Disable a Capture Instance for a Table template

-- ===============================================

USE MyDB
GO

EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@capture_instance = N'dbo_MyTable'
GO