SQL Server Performance

Snapshot Replication Blocking

Discussion in 'Performance Tuning for SQL Server Replication' started by simondm, Nov 3, 2004.

  1. simondm New Member

    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

Share This Page