Shouldn't be, as I don't think lock granularity can escalate all the way up
to db level. There are some weird possibilities like excessive buffer
latches wait due to really bad I/O subsystem, or you run out of worker
threads, or contention from page allocation. The best thing to do really is
to start cracking through the output of sp_locks (or select from
master..syslockinfo).
-Raymond
[quoted text, click to view] "Chris" <Chris@discussions.microsoft.com> wrote in message
news:E1759144-8D4A-402C-8576-6ACF2D1BFF1C@microsoft.com...
>
> All triggers have NOT FOR REPLICATION
>
> All foreign keys are dropped when I intialize subscriber w/ restore of
> backed up pub db. FK's cause repl to fail.
>
> I will look at sp_locks - perhaps locks are escalating to db locks???
>
>
>
> "Raymond Mak [MSFT]" wrote:
>
>> Hi Chris,
>>
>> My best guess is that you probably have [cascading?] fk between the
>> tables
>> or you have triggers that spans multiple tables. A more accurrate way to
>> find out what is actually behind the blocking is to simply dig through
>> the
>> output of sp_locks.
>>
>> -Raymond
>>
>> "Chris" <Chris@discussions.microsoft.com> wrote in message
>> news:8A0ACF06-796C-41B8-851C-2F9057285436@microsoft.com...
>> >
>> > SQL2000 sp4 pub, sql2k sp4 distr, sql2k sp4 subscriber (across wan - t1
>> > tunnel)
>> >
>> > Transactional replication.
>> >
>> > Blocking occuring on subscriber causing distr agent to fail 2 to 3
>> > times
>> > daily. Distribution server only process connecting to subscriber,
>> > blocking
>> > &
>> > blocked spid numbers different. E.g.
>> >
>> > spid 73 (sp_MSdelAcctgBals) - blocking
>> > spid 64 (sp_MSupdOrderInfo) - blocked by 73
>> >
>> > Why 2 different spids (multi-threading?) Why does blocking occur ?
>> > Tables
>> > not related to each other, never updated together in a transaction at
>> > pub...
>> >
>> > How to resolve?
>> >
>> > thanks. chris
>>
>>
>>
