2

I am using the SQL Change Data Capture feature to retrieve historical data from my tables.

During testing, I noticed that when I insert/update a row and query the change data right afterwards (see my earlier question on how I do that), the result will turn up empty.

I played around with it and noticed that if I wait 10 seconds between the data manipulation and the history query, the history will be up to date.

I am aware that CDC functionality is based on the transaction logs somehow and that this probably is the reason why there is a delay between data changes and the cdc history table being updated.

Now my question:

  • Is this delay somewhere explained in the CDC documentation? I could not find any mention of it.
  • Is there a way to manually make sure that the history table is up to date before I query it? (somehow triggering CDC to check the logs?)
  • How does CDC work together with transactions? Will I for example be able to query historical data that I have overwritten in the same transaction? Or does the transaction first have to commit for the cdc data to become availalbe?
Improve this question

1 Answer 1

Reset to default
2

CDC relies on a SQL Server Agent job to capture information from the transaction log. You can customize the job parameters by updating msdb.dbo.cdc_jobs - there are multiple properties as described in this Books Online topic:

  • maxtrans
  • maxscans
  • continuous
  • pollinginterval

That doc has some information about these parameters, as does this white paper.

But really, CDC is not meant to be a real-time system, and you should use caution adjusting these parameters too much to make it so. Most people acknowledge that you need to wait to let CDC catch up (see here and here). If 5-10 seconds is not fast enough for you, you might be using the wrong technology.

As for the separate transaction question, this is something you could test, no?

Improve this answer
2
  • As for the separate transaction question, this is something you could test, no? Indeed, that was what I was doing, when the delay problem turned up and distorted my results. Now I am not sure anymore if I am testing right. ;) But you are right, I will run some tests with a big enough delay and hope I am not missing something.
    – magnattic
    Commented Mar 26, 2014 at 15:29
  • So far my tests seem to indicate that the transaction has to be committed before the history data appears in the CDC table. So I guess there is no way to read it from the same transaction.
    – magnattic
    Commented Mar 26, 2014 at 15:36

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.