none
Change Data Capture: Experiencing Delay

    Question

  • Hello Community,

    I am experiencing an anomaly with Change Data Capture that is turning up empty with online searches.  Essentially, I am attempting to change data, and then immediately call the fn_cdc_get_all_changes_* function to get the changes I just made.

    The issue I am running into is that the changes are not immediately available when I make that call.  I have to sleep the current thread 3.5-5 seconds before the changes are registered with SQL Server.

    This seems very abnormal to me, so I wanted to check in with the forums here to see if maybe someone can shed some light on the matter.  Is CDC scheduled, perhaps?  I'd like to see if there's a "force refresh" mechanism that I can call to ensure CDC is caught up before I make the call.

    Thank you for any assistance you can provide!

    Michael

    Friday, March 14, 2014 7:35 PM

Answers

All replies

  • The refresh is scheduled as a job that runs continuously, with some kind of wait.

    I don't know of a way to force a refresh.  Normally, I glance at my email (scan subjects, no more) and then check again.

    I think that the idea is they don't want CDC to cause blocking with the transactions it is tracking.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, March 14, 2014 8:31 PM
  • Thank you Russ for your reply.  It would be great to know the job to call, or any mechanism to get CDC in line before it is accessed.  Seems sorta silly to have to do that (you would think the fn_cdc_get_all_changes_* call would do this for you).  I realize you said you didn't know, but if someone on the forum here knows how to do this, I would greatly appreciate it!

    In any case, thanks again for the heads up.

    Friday, March 14, 2014 10:29 PM
  • Hi Michael,

    The CDC latency is defined as the elapsed time between a transaction being committed on a source table and the last captured transaction being committed on the change table. To decrease the latency, we can change the following parameters of the sys.sp_MScdc_capture_job stored procedure of the target database:

    • Increase maxscans - (default 10) the number of scans performed between each polling interval
    • Increase maxtrans - (default 500) the number of transactions to grab with each scan
    • Decrease pollinginterval - (default 5 seconds) the amount of time to wait between log scans

    References:

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Friday, March 21, 2014 8:18 AM
    Moderator
  • AWESOME!  Exactly what we were looking for.  Thank you, Mike!
    Friday, March 21, 2014 11:52 AM
  • Hi Michael,

    Thank you for your posting. I am glad to be of help.

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, March 24, 2014 1:19 AM
    Moderator