Are the CDC tables written in a single "transaction"

Question

I will be writing a process that uses the CDC mechanism.  Can I guarantee that all of the operations involved in the "transaction" will be available in the CDC generated table at one time, or is it possible to query the same LSN range and have different results returned (i.e. the CDC batch job has only processed half of the transaction log)?

I would imagine not, but my boss want's a definitive answer.

Answer 1

Hi leifordjr,

It depends on what logic used for the mechanism. In CDC procedure, all of the operations that are used to poplulate the database change tables are invoked in a sys.sp_MScdc_capture_job stored procedure at one time. However, some process are outside of the stored procedure execution, for example, the changes are recorded into the transaction log and read by the capture instance. So, you must first desgin a very clear login on how to process your change data capture and then can definitively answer your boss.

 

Regards,
Jerry

TechNet Subscriber Support in forum
If you have any feedback on our support, please contact  [e-mail]