Capture job does not see an update statement in SQL Server database

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #18377
    Pallavik
    Keymaster

    Problem Summary:  Capture job does not see an update statement in SQL Server database

    Description:

    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-

    Pre-requisites

    1. Added table test3 to the channel cdcdemo.
    2. Initialized this table with all the options so that CDC is enabled for this table

    You can check if CDC is enabled by running below statement against the database in which table test3 exists. In this case this table resides in the database ‘bidb’.

    sys.sp_cdc_help_change_data_capture

     

    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.

    sys.sp_cdc_help_change_data_capture

    You will get error message like 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 insert and update statement on the SQL server

    But only insert was captured

    Workaround:

    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 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.

     

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us