June 16, 2009 at 10:36 pm
I'm trying to troubleshoot a recurring blocking problem on my replicated database. Both publisher and subscriber are 2k5 (sp2).
The devs have a process that when it get's to it's insert phase blocks the replication distribution agent.
As a result latency skyrockets. After going over their sproc, all but one insert are smaller temp tables, and their selects are using the nolock hint. The one insert statement that isn't a temporary table is writing to a table on the subscriber, but that table is not being replicated.
On the surface this situation seems like it should not create a block, but I'm wondering if anyone has some insight as to what could be causing it.
June 17, 2009 at 6:05 am
When the distribution agent is waiting because it is blocked have you run sp_lock (passing the spid of the distribution agent) to see which table it is being blocked on? Also, it would be useful to see the code of the stored procedure if that is possible.
Mike
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply