CDC_Capture Job Failure

asked 2020-09-04T02:44:54.557+00:00
Navin Dondapati 831 Reputation points

Hi Guys,

We enabled CDC on SQL server 2016; and observed CDC job fails intermittently; what happen CDC capture job is in fail state for more than 2 days; when we restart job, does it catch all the delete/update/insert records?

We observe our down stream are not in sync when CDC capture job fail; And how we overcome the issue.

Regards,
Navin

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,454 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2020-09-04T10:35:42.763+00:00
    Cris Zhan-MSFT 6,561 Reputation points

    Hi,

    Starting and stopping the capture job does not result in a loss of change data. It only prevents the capture process from actively scanning the log for change entries to deposit in the change tables. A reasonable strategy to prevent log scanning from adding load during periods of peak demand is to stop the capture job and restart it when demand is reduced.
    https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

    If the CDC capture job fails to work, and even if the database recovery model is Simple, the database log file will grow rapidly, because even if the log file is CHECKPOINT, log truncation will not continue until the CDC capture process will collect all changes waiting to be captured .

    View the job history to check the error message for the failure.

    ===============================================

    If the response helped, do "Accept Answer" and upvote it.


  2. answered 2020-09-09T02:08:37.957+00:00
    Navin Dondapati 831 Reputation points

    did not work, we are unable to capture delete/update or insert records even after restarting the job.