8

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
knightscharge
  • 382
  • 2
  • 6
  • 14

1 Answers1

10

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 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? – knightscharge Feb 19 '13 at 13:25
  • Maybe this is updated, or misleading, but [I found some documentation](https://msdn.microsoft.com/en-us/library/hh710064.aspx) 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... – David Spence Jun 09 '15 at 09:32
  • Seems like your answer is accurate and the documentation is confusing, no column is automatically added to the CDC table :( – David Spence Jun 09 '15 at 15:42
  • David, do not confuse CDC with ChangeTracking, these are two distinct features. See also https://www.littlekendra.com/2010/06/23/cdcvsct/ – codetuner Mar 29 '17 at 12:53
  • Try restarting SQL Server Agent for CDC tracking to reflect the changes.. – HydPhani Aug 02 '22 at 12:27