How to Enable Change Data Capture (CDC) in SQL Server

May 25, 2026

–

Change Data Capture (CDC) allows SQL Server to track inserts, updates, and deletes on selected tables by reading the transaction log and storing changes in system tables.

From a DBA point of view, CDC is easy to enable but easy to underestimate. It adds background jobs, ongoing storage growth, and operational overhead that needs to be understood before enabling it on a production system.

This post shows how to enable CDC safely at both the database and table level, with the checks you should do first.

When CDC Is Useful

CDC is commonly enabled when:

You need to track historical data changes
A downstream system consumes row-level changes
Audit or compliance requirements exist
You want visibility into how data changes over time

If you’re unsure whether CDC fits your use case, review Microsoft’s documentation first and test on a non-production system.

👉 Microsoft Docs: Change Data Capture (CDC)
https://learn.microsoft.com/sql/relational-databases/track-changes/about-change-data-capture-sql-server

Before You Enable CDC

Before enabling CDC on any database, check two things.

1. Is CDC already enabled?

This avoids confusion in larger or inherited environments.

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

2. Is the database owner set to `sa`?

CDC requires the database owner to be sa.
If it isn’t, enabling CDC will fail.

-- Change database owner to 'sa'
ALTER AUTHORIZATION 
ON DATABASE::[demoBlog] 
TO [sa];
GO

👉 Microsoft Docs: sys.sp_cdc_enable_db
https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-db-transact-sql

Enable CDC on a Database

Once prerequisites are met, enabling CDC at the database level is straightforward.

-- Enable CDC for a database
USE [demoBlog];
GO

EXEC sys.sp_cdc_enable_db;
GO

SQL Server creates several system tables under the cdc schema to manage metadata and change tracking.

Verify it worked:

-- Verify CDC is enabled for a db
SELECT 
    name,
    is_cdc_enabled
FROM sys.databases;

Enable CDC on a Table

CDC must be enabled per table.

First, check whether a table is already tracked:

-- Check if CDC is enabled for a table
SELECT 
    name,
    is_tracked_by_cdc
FROM sys.tables;

Filegroup Consideration (Important)

By default, CDC change tables are created in the database’s primary filegroup.

For databases that stay in service for a long time, this can become a problem. CDC tables grow continuously and can create unexpected storage pressure if they share space with core data files.

Microsoft recommends isolating CDC data into a separate filegroup when running CDC beyond short-term testing.

If you plan to do this, create the filegroup before enabling CDC on tables.

(There is a separate DBA Engineering post that covers CDC filegroup design in detail.)

👉 Microsoft Docs: sys.sp_cdc_enable_table
https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql

Enable CDC for a Table

-- Enable CDC for a table
USE [demoBlog];
GO

EXEC sys.sp_cdc_enable_table
    @source_schema   = N'dbo',
    @source_name     = N'posts',
    @role_name       = NULL,
    @filegroup_name  = N'DemoBlog_CT'; -- optional but recommended
GO

When this runs successfully:

CDC change tables are created
SQL Agent jobs are started automatically:
- cdc.<database>_capture
- cdc.<database>_cleanup

These jobs must remain enabled for CDC to function.

How CDC Works Behind the Scenes

CDC reads committed transactions from the transaction log
Changes are written asynchronously into change tables
Inserts, updates, and deletes are recorded
Schema changes do not automatically update CDC configuration

If both replication and CDC are enabled, the replication log reader handles population of CDC tables.

Verifying CDC Is Working

At a high level:

CDC reads committed transactions from the transaction log
Changes are written asynchronously to CDC tables
Inserts, updates, and deletes are captured
Schema changes do not automatically update CDC configuration

If both replication and CDC are enabled, the replication log reader handles CDC population.

Operational Warnings (Read This)

Before enabling CDC in production, be aware:

CDC increases disk usage over time
Change tables grow until cleaned up
SQL Agent job health matters
Schema changes often require CDC to be disabled and re-enabled
Poor planning can cause unexpected storage issues

CDC is easy to turn on and easy to forget about. Treat it as an ongoing operational feature, not a one-time switch.

Blog Categories

Comments

Leave a Reply Cancel reply