I have enabled CDC in SQL Server and it generally works well but sometimes the changes are not recorded for some reason. And only for a couple of records. But then if I issue an
UPDATE from SSMS, the change is recorded.
I'm wondering if it might have something to do with the SQL Server Agent jobs; maybe those jobs fail and because of that the change is not saved?
I have checked the job history in SQL Agent (for
cdc.myDatabase_cleanup) but couldn't find any failures.
What should I investigate to find the cause of the issue?
SQL Server Details:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)