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

Replication Message

Does anyone know what causes this message?
The row was inserted at 'CVTDEESQL005.ZenithSQL_L_060104' but could not be
inserted at 'ICK24BQZ0J.Zenith'. Procedure or function sp_MSsetrowmetadata
has too many arguments specified.
Our conflict log seems to be full of them
Does this apply?
http://support.microsoft.com/kb/319258/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
"Spike" <Spike@.discussions.microsoft.com> wrote in message
news:582616D9-62FB-4E73-A83F-01B3C835BBB6@.microsoft.com...
> Does anyone know what causes this message?
> The row was inserted at 'CVTDEESQL005.ZenithSQL_L_060104' but could not be
> inserted at 'ICK24BQZ0J.Zenith'. Procedure or function sp_MSsetrowmetadata
> has too many arguments specified.
> Our conflict log seems to be full of them
>
|||Hi Hilary, I don't think so as we do have service pack 3 applied. However,
it seems to be tied up with a new subscription database that we issued to the
users.
My main concern is to not lose any of changes that have been stored as
conflicts.
kind regards
Spike
"Hilary Cotter" wrote:

> Does this apply?
> http://support.microsoft.com/kb/319258/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
>
> "Spike" <Spike@.discussions.microsoft.com> wrote in message
> news:582616D9-62FB-4E73-A83F-01B3C835BBB6@.microsoft.com...
>
>
|||Spike,
Check your merge agents in the replication monitor, you may see errors
related to space in the database primary tables. We started coming up with
this error over a year ago and after fighting with MS for a long time they
came back that is was a bug in the merger replication. If you view the
replication conflicts for your published database you will probably find a
lot of them. You should be able to resolve them by either keeping the winning
change or resolving, which ever columb has data in it (the other side should
read somthing about the data was changed at both locations, the update
won/lost.. (sorry can't remember the exact verbage). This should add any lost
data back to your database.
I am not sure what SP (if any) that microsoft added this fix to, they sent
us 2 patches to resolve the issue.
Check your database sizes and make sure they are not approaching the limits.
You may need to allocate more space for the primary tables.
Hope this helps you out.
Gary
"Spike" wrote:
[vbcol=seagreen]
> Hi Hilary, I don't think so as we do have service pack 3 applied. However,
> it seems to be tied up with a new subscription database that we issued to the
> users.
> My main concern is to not lose any of changes that have been stored as
> conflicts.
> kind regards
> Spike
> "Hilary Cotter" wrote: