CDC table not working after adding new columns to the source table

Question

Two new columns were added to our source table while CDC was still enabled on the table. I need the new columns to appear in the CDC table but do not know what procedure should be followed to do this? I have already disabled CDC on the table, disabled CDC on the DB, added the new columns to the cdc.captured_columns table, and enabled CDC. But now I am getting no data in the CDC table!

Is there some other CDC table that must be updated after columns are added to the source table? These are all the CDC tables under the System Tables folder:

• cdc.captured_columns <----- where I added the new columns
• cdc.change_tables
• cdc.dbo_myTable_CT <------ table where change data was being captured
• cdc.ddl_history
• cdc.index_columns
• cdc.lsn_time_mapping
• dbo.systranschemas

Process

Answer 1

I recommend reading Tracking Changes in Your Enterprise Database. Is very detailed and deep. Among other extremly useful bits of info, there is such as:

DDL changes are unrestricted while change data capture is enabled. However, they may have some effect on the change data collected if columns are added or dropped. If a tracked column is dropped, all further entries in the capture instance will have NULL for that column. If a column is added, it will be ignored by the capture instance. In other words, the shape of the capture instance is set when it is created.

If column changes are required, it is possible to create another capture instance for a table (to a maximum of two capture instances per table) and allow consumers of the change data to migrate to the new table schema.

This is a very sensible and well thought design that considers schema drift (not all participants can have the schema updated simultaneously in a real online deployment). Having a multi-staged approach (deploy DDL, capture new CDC, upgrade subscribers, drop old CDC capture) is the only feasible approach and you should follow suit.

Chyou

I did read that but was hoping to keep the same CDC table. Is this the "standard" procedure that is followed? And dropping the old CDC table will have no adverse effects on the capture? – Process

Maybe this is updated, or misleading, but I found some documentation which suggests values of new columns will be tracked, but not the addition - "If a new column is added to the change tracked table, the addition of the column is not tracked. Only the updates and changes that are made to the new column are tracked." I'm going to try it out... – Grenade

Seems like your answer is accurate and the documentation is confusing, no column is automatically added to the CDC table :( – Grenade

David, do not confuse CDC with ChangeTracking, these are two distinct features. See also littlekendra.com/2010/06/23/cdcvsct – Psychoanalysis

Try restarting SQL Server Agent for CDC tracking to reflect the changes.. – Salute