Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Aftere a few night's check , I figured out what's happening in the master sqlserver database .sqlserver cdc has two work process. one is capture data process,the other is cleanup stale data process. the cleanup stale data process runs at 2:00 am every day,the capture data process alwayrs run 。when I run the sqlserver cdc pipeline, that is the query process. there is no conflict between the query process and the capture data process . But when the cleanup stale data process begin to work , It will need executive lock in some resource,while this resource may be locked(shared lock) by our query process。then the cleanup stale data process will be blocked by our query process(sqlserver cdc pipeline)。By far ,everything is ok,the cleanup stale data process just need to acquire the executive lock in some resource after the query process finish query。

Then the problem occurs , the query process always occupy the shared lock. then cleanup stale data process always blocked。 Finally I found there is a config in the sqlserver cdc origin advanced tag. the auto commit tag, this tag's default value is disable , I don't know why the default value is disable, to figure out above problem , I checked everything I can think . At last To figure out this I just need to change a default config . So I advise that this config should be enable by default In case other people run into the same problem and spend a large amount time to tackle it.