Ads

16 November 2026

SQL Server Change Data Capture


Change Data Capture (CDC) in SQL Server tracks INSERT , UPDATE and DELETE activity on your tables by reading the transaction log asynchronously and writing the changes to dedicated history tables

Pre-requisites:

SQL Server Edition: CDC is available on Enterprise, Developer and Standard(2016 and later) and  editions , not available on express edition.

SQL Server Agent: To Capture and Cleanup, once we enable CDC two jobs will create automatically.

   1) cdc.TEST_capture

    2) cdc.TEST_cleanup

Permissions: sysadmin or db_owner

Recovery Model: Full or Bulk logged 

Disk Space: CDC changes table store a copy of every changed row. Keep additional storage.

Enable CDC on the Database:

    USE TEST

    EXEC sys.sp_cdc_enable_db;

Verify CDC enabled or not

    SELECT name, is_cdc_enabled 

    FROM sys.databases 

    WHERE name = 'TEST';




Enable CDC on a Specific Table

    USE TEST;
     EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Table_8',
    @role_name     = NULL;
   
 Test and Query the Change Table

When you enable the CDC on table , SQL server will create a shadow change table named with cdc.dbo_table_8_CT

if you see _$operation column with 1,2,3 and 4 
    
    1 Indicate a DELETE
    2 Indicate a INSERT
    3 Shows the row values before a UPDATE
    4 shows the row values after an UPDATE




Disable CDC 

Verify CDC enabled or not 






    -- Disable CDC for a single table
    EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'table_8',
    @capture_instance = N'dbo_table_8';
    
-- Disable CDC for the entire database
-- Automatically removes all table instances and jobs

EXEC sys.sp_cdc_disable_db;
GO

name is_cdc_enabled
TEST 0