2

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) 
 (Hypervisor)
  • Are you using Get_All_Changes or Get_Net_Changes when you're seeing this? – Sean Gallardy Aug 18 '14 at 15:19
  • CDC processes changes asynchronously. Does your logic account for that correctly? – Jon Seigel Aug 18 '14 at 17:43
  • @SeanGallardy: I'm retrieving data directly from the cdc table – SzilardD Aug 19 '14 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 Aug 19 '14 at 5:38
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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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