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_capture and 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) 
  • Are you using Get_All_Changes or Get_Net_Changes when you're seeing this? Commented Aug 18, 2014 at 15:19
  • CDC processes changes asynchronously. Does your logic account for that correctly?
    – Jon Seigel
    Commented Aug 18, 2014 at 17:43
  • @SeanGallardy: I'm retrieving data directly from the cdc table
    – SzilardD
    Commented Aug 19, 2014 at 5:35
  • @JonSeigel: Yes, I know that changes are processed in async mode, but that's not the problem since there are no change records in the cdc table.
    – SzilardD
    Commented Aug 19, 2014 at 5:38

1 Answer 1


The problem was that the retention period was set to the default of 3 days.

I have extended it to one year using this script:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 525600; --set data retention for 1 year

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.