Share via

CDC (Change Data Capture) does not work with restored Databse in sql server 2017.

Imran pyarali 1 Reputation point
2022-02-07T20:13:42.403+00:00

I have restored a CDC enabled Database in sql server 2017 with keep_cdc. The sql server agent job (capture, cleanup) job was created. However, the data is not recorded in CDC Tables. Upon probing the issue on internet, I came to know that it’s a known bug that was fixed by Microsoft in their Cumulative Update 4 Package. But the issue still persists.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,461 Reputation points
    2022-02-08T02:56:25.357+00:00

    Hi @Imran pyarali ,

    You also need to add the Capture and Cleanup jobs using the following commands in the appropriate database. A capture job is used to populate the database change table and a cleanup job is responsible for a change table cleanup. The capture and cleanup jobs are created when a replication is enabled for the first time and initiated change data capture for a table. 

    exec sys.sp_cdc_add_job 'capture'  
GO  
exec sys.sp_cdc_add_job 'cleanup'  
GO

    For more information, please read this article: Restoring Database with CDC Enabled.

    BTW, the latest Cumulative Update for SQL Server 2017 is CU28. Although this is not the point, there is always nothing wrong with installing the latest CU.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Seeya Xi-MSFT 16,461 Reputation points
    2022-02-22T02:53:30.267+00:00

    Hi @Imran pyarali ,

    Please see this article which provides the solution about your error.
    https://repltalk.com/2019/01/03/cdc-log-scan-fails-with-could-not-open-file-control-block/

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments