A capture job does not see an update transaction with a Sql*Server source.
When capturing from SQL Server database if CDC is disabled on the database, capture job will not see the update made on this table
Below is a demonstration on this-
- Added table test3 to the channel cdcdemo.
- Initialized this table with all the options so that CDC is enabled for this table
You can check if CDC is enabled by running the below statement against the database in which table test3 exists. In this case, this table resides in the database 'bidb'.
Another way of checking this is by checking the System Tables of this database
The table hvr_2087014516_CT is the CDC table for table test3. HVR created the table with name hvr_<source_object_ID>_CT.
When the change table is present for test3 table inserts, updates and deletes are captured
When CDC is disabled for this database you will see that insert and deletes are captured but update is not.
You can check if CDC is disabled by running below command on SQL Server source against database bidb.
You will get an error message like the one below:
Msg 22901, Level 16, State 1, Procedure sp_cdc_help_change_data_capture, Line 19
The database 'bidb' is not enabled for Change Data Capture. Ensure that the correct database context is set and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view.
I executed the insert and update statement on the SQL server
But only insert was captured
To resolve this issue make sure CDC is enabled on the table. This can be done by running HVR initialize with supplemental logging and Scripts and Jobs option.
Also since CDC was not enabled it is required that a full refresh is done for the table that missed updates.
Please note Capture rewind does not work in this case because the CDC tables were not present and this change has not been logged in the change tables.