Replication that causes blocking of process

Hi ,
My Replication also got this timeout error while running the last command :
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
blocking
SPid73 --> which is doing the CALL sp_MSdel_SL030100
How can i resolve it as i am afraid that if i kill any process the sync is
out and i would be forced to re-create the snapshot and also shldn't the
SP_MSdel be done first ?
apreciate ur advise
Message posted via http://www.droptable.com
This looks like an update which is being performed as a delete insert pair.
Study what the procs are doing and see if it can't benefit from new indexes
of updating the indexes.
You might also want to look at this trace flag.
http://support.microsoft.com/kb/238254/en-us
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5a944df8034f0@.uwe...
> Hi ,
> My Replication also got this timeout error while running the last command
> :
> {CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL
> sp_MSins_SL030100
> (N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
> i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
> blocking
> SPid73 --> which is doing the CALL sp_MSdel_SL030100
> How can i resolve it as i am afraid that if i kill any process the sync is
> out and i would be forced to re-create the snapshot and also shldn't the
> SP_MSdel be done first ?
>
> apreciate ur advise
> --
> Message posted via http://www.droptable.com
|||Hi,
First of all tks for the links provided.
as menitoned in the link it's doing a so-called "deferred" update when the
unique constraint (i.e the PK) has been changed
However , from the stored procedure below
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19
BJX0047 & 26.01.06AF are the 2 PKs' values and both the DELETE/INSERT are
showing the same values, so i am confused
appreciate ur further advice
btw : after leaving this replication for the whole nite , it somehow resolved
by itself
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>This looks like an update which is being performed as a delete insert pair.
>Study what the procs are doing and see if it can't benefit from new indexes
>of updating the indexes.
>You might also want to look at this trace flag.
>http://support.microsoft.com/kb/238254/en-us
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com