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

sql-server change-data-capture
knightscharge picture knightscharge · Feb 19, 2013 · Viewed 10.2k times · Source

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

Answer

Remus Rusanu picture Remus Rusanu · Feb 19, 2013

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.

Related questions

Could not update the metadata that indicates database X is enabled for Change Data Capture. The error returned was 15517

I use SQL Server 2008 and AdventureWorkDB. I want enable Change Data Capture in my database. I execute this command : USE [AdventureWorksDB]; GO EXEC sys.sp_cdc_enable_db ; But i get this error : Could not update the metadata that indicates …

Read More
CDC is enabled, but cdc.dbo<table-name>_CT table is not being populated

I have enabled CDC using the following steps: exec sys.sp_cdc_enable_db; exec sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'table_name', @role_name = N'CDC_Access', @supports_net_changes = 1; I can see that a CT …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More