SQL Server Performance Forum – Threads Archive

Snapshot Replication Blocking

I’ve got a problem with a pull subscription to a snapshot publication. Whenever I add the subscription I get a blocking problem and it never completes the task (I have to kill it). When it is adding the subscription through the wizard you get a small status box appear, it gets to the last step (updating publisher I think) then is blocked by user selecting on the tables in the publication. These selects are thick and fast, may 20 or so a second. Now oddly the new selects coming now start getting blocked by the subscription SPID. It looks like a deadlock scenerio with everyone waiting for each other but SQL does not recognise it as a deadlock. My question is why does it need to do anything with the publication table? Surley this is for teh publication to manage which has already been setup successfully?? I tried looking at sp_addsubscription which is what I can see in Profiler being fired but it hasn’t really helped, I can’t see anything effecting the tables directly. I think it may be trying to update sysobjects to say that the object is replicated (though I would have expected the publication to have done this already). Would it need a scheme lock to do this. Any advice or article you can offer would be greatly appreciated. Simon

---

]]>