Sql-server – Delayed availability of historical data when using CDC

change-data-capturesql serversql-server-2012

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?

Best Answer

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?

Related Question