SQL Server does not automatically track certain DDL changes in the Change Data Capture log after CDC was enabled on the table. This includes adding new columns to a table after CDC was enabled for the table. To make the CDC log reflects the DDL changes on the source table, it must disable CDC and re-enable CDC on the CDC enabled table. Here comes an example:
USE master
GO
-- Create a database
CREATE DATABASE DbTestCDC;
GO
USE DbTestCDC;
GO
-- Create a table
CREATE TABLE TableTestCDC
(
ID int NOT NULL PRIMARY KEY,
FirstName varchar(30),
LastName varchar(30),
Email varchar(50)
);
-- Enable CDC for the current database
EXEC sys.sp_cdc_enable_db;
GO
SELECT name, is_cdc_enabled FROM sys.databases;
GO
-- Enable CDC for the specified table in the current database
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'TableTestCDC',
@role_name = NULL,
@supports_net_changes = 1
GO
-- Check CDC enabled on that table
EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';
-- Check CDC retention (default 4320 minutes = 3 days)
SELECT DB_NAME(database_id), * FROM msdb.dbo.cdc_jobs;
-- Testing
INSERT INTO TableTestCDC Values (1, 'Jorge', 'Ramos', 'ramos@yahoo.com');
INSERT INTO TableTestCDC Values (2, 'Andrew', 'Strudwick', 'andrew@yahoo.com');
INSERT INTO TableTestCDC Values (3, 'Thomas', 'Tuchel', 'tuchel@gmail.com');
DELETE FROM TableTestCDC WHERE ID = 1;
UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ' WHERE ID = 2;
DELETE FROM TableTestCDC WHERE ID = 3;
SELECT * FROM TableTestCDC;
GO
WAITFOR DELAY '00:00:05';
GO
-- Query CDC data
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,
CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,
*
FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');
GO
-- ALTER TABLE on the source table, e.g. add a new column
ALTER TABLE dbo.TableTestCDC ADD NewCol varchar(50) NULL;
GO
-- Disable CDC on that table, and then re-enable CDC to reflect the table DDL changes
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'TableTestCDC',
@capture_instance = N'all';
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'TableTestCDC',
@role_name = NULL,
@supports_net_changes = 1
GO
EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';
-- Testing (with New column)
INSERT INTO TableTestCDC Values (4, 'Jorge', 'Ramos', 'ramos@yahoo.com', 'NewCol 1');
INSERT INTO TableTestCDC Values (5, 'Andrew', 'Strudwick', 'andrew@yahoo.com', 'NewCol 2');
INSERT INTO TableTestCDC Values (6, 'Thomas', 'Tuchel', 'tuchel@gmail.com', 'NewCol 3');
DELETE FROM TableTestCDC WHERE ID = 4;
UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ', NewCol = 'New Col X' WHERE ID = 5;
DELETE FROM TableTestCDC WHERE ID = 6;
SELECT * FROM TableTestCDC;
GO
-- Query CDC data
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,
CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,
*
FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');
GO
