CDC_Capture Job Failure

Question

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

Answer 1

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.

Answer 2

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