Row not found at subscriber

I posted this yesterday but in reply to an earlier post and thought that
perhaps it was overlooked.
Error: The row was not found at the Subscriber when applying the replicated
command.
(Source: AFNBLDRPT1 (Data source); Error number: 20598)
Both the publisher and subscriber are on
sp3. The publisher was just moved to this server the evening of 6/22 and was
working
fine before on its old machine (sp2, 2 pretty slow processors). The new
server has 2 1 ghz HT processors. I don't know if that has anything to do
with this or not but I can't come up with much else.
We dropped the old publication and created a new publication from scratch.
The snapshot applies just fine. The distribution agent then errors out on
various records with the error: The row was not found at the Subscriber when
applying the replicated command. We turned on verbose logging, figured out
the exact row in question, and confirmed that it exists in both databases.
So, we set up a second subscription to this publication to tinker with. We
added the switch to prevent this error from being generated
(added -skiperrors 20598 to the run agent command) but then started getting
Cannot insert duplicate key row in object 'Accounts' with unique index
'MSCCSPK_20040623103007033'.
From this article:
http://support.microsoft.com/default...nowebcontent=1 I
looked into the table design and confirmed that the fields being updated are
not part of a clustered or unique index (or part of the PK). However, with
nothing else to I went ahead and turned on the traceflag to force updates to
replicate as updates instead of inserts/deletes. I dropped the subscription
and created a new one and after several timeouts (?) it finally errored with
the same duplicate key error.
I'm not sure what else to try.
Michelle
Michelle,
to find out more about the nature of this error, can you use
sp_browsereplcmds when the error occurs and then run the stored procedure
call in QA.
Regards,
Paul Ibison
|||I've since started over again. Could using immediate updating vs. queued
updating cause these sorts of conflicts?
I didn't actually run the command but I did query the database for the
command and reviewed what was trying to execute.
If it errors out again this time around, I will try running the command
myself to see the results.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O3NeTQiWEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Michelle,
> to find out more about the nature of this error, can you use
> sp_browsereplcmds when the error occurs and then run the stored procedure
> call in QA.
> Regards,
> Paul Ibison
>
|||I can't be certain that I had previously selected immediate updating. I
could find no way to view that setting. However, it's possible that I did. I
most definitely selected queued updating this time around and my issues are
gone. At least they appear to be for now. I only had about 10 minutes of
transactions. However, when it was breaking before it was almost immediately
after the transactions started.
Immediate updating is somewhat misleading without careful review of the
meaning. One might think that queued updating with transactional replication
is not desired if you're looking for 'real-time' data. However, it seems to
only come into play when connectivity between the publisher and subscriber
is lost.
I would suggest to others with this problem that they create a new
publication using queued updating to see if that resolves their issue.
"michelle" <michelle@.nospam.com> wrote in message
news:O%236jnxiWEHA.3716@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I've since started over again. Could using immediate updating vs. queued
> updating cause these sorts of conflicts?
> I didn't actually run the command but I did query the database for the
> command and reviewed what was trying to execute.
> If it errors out again this time around, I will try running the command
> myself to see the results.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O3NeTQiWEHA.2816@.TK2MSFTNGP11.phx.gbl...
procedure
>
|||it is unlikely that queued or immediate updating caused this problem.
This problem is caused by the publisher and subscriber being in an
inconsistent state, or having data consistency problems. In other words they
are out of sync.
To resolve these problems you must verify that only the replication process
is making changes to the data that is replicated to the subscriber. In other
words are users directly making updates on the subscriber? If so, have you a
partitioing scheme that will ensure that these users will never update the
data that is replicated from the publisher?
You must also verify that you started off with a subscriber that was in sync
with the publisher. The best way to do this is 1) use the subscriber does
not have the schema and data option when you deploy your subscription. 2) if
you are doing a no-sync subscription, ensure you run some data validation
before you start up your distribution agent.
I would advise you to run a data validation now, and see exactly how out of
sync you are. It could be 1 record, it could be many. Then perhaps
reinitialize and start again. If you have scads of free disk space, run
profiler on your subscriber to try to trap other users accessing the
subscriber and determine what they are doing. If you can change all
passwords on the subscriber, and see who comes to you complaining about not
being able to update data.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"michelle" <michelle@.nospam.com> wrote in message
news:O%236jnxiWEHA.3716@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I've since started over again. Could using immediate updating vs. queued
> updating cause these sorts of conflicts?
> I didn't actually run the command but I did query the database for the
> command and reviewed what was trying to execute.
> If it errors out again this time around, I will try running the command
> myself to see the results.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O3NeTQiWEHA.2816@.TK2MSFTNGP11.phx.gbl...
procedure
>
|||This was a fresh snapshot (schema and data) being replicated to a new
suscriber. The transactional replication started failing almost immediately
after the snapshot was applied. Nobody is updating the subscriber. I will
validate the data but I'm not getting any errors now.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OQRgL0qWEHA.1000@.TK2MSFTNGP12.phx.gbl...
> it is unlikely that queued or immediate updating caused this problem.
> This problem is caused by the publisher and subscriber being in an
> inconsistent state, or having data consistency problems. In other words
they
> are out of sync.
> To resolve these problems you must verify that only the replication
process
> is making changes to the data that is replicated to the subscriber. In
other
> words are users directly making updates on the subscriber? If so, have you
a
> partitioing scheme that will ensure that these users will never update the
> data that is replicated from the publisher?
> You must also verify that you started off with a subscriber that was in
sync
> with the publisher. The best way to do this is 1) use the subscriber does
> not have the schema and data option when you deploy your subscription. 2)
if
> you are doing a no-sync subscription, ensure you run some data validation
> before you start up your distribution agent.
> I would advise you to run a data validation now, and see exactly how out
of
> sync you are. It could be 1 record, it could be many. Then perhaps
> reinitialize and start again. If you have scads of free disk space, run
> profiler on your subscriber to try to trap other users accessing the
> subscriber and determine what they are doing. If you can change all
> passwords on the subscriber, and see who comes to you complaining about
not
> being able to update data.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:O%236jnxiWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> procedure
>
|||This was a fresh snapshot (schema and data) being replicated to a new
suscriber. The transactional replication started failing almost immediately
after the snapshot was applied. Nobody is updating the subscriber. I will
validate the data but I'm not getting any errors now.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OQRgL0qWEHA.1000@.TK2MSFTNGP12.phx.gbl...
> it is unlikely that queued or immediate updating caused this problem.
> This problem is caused by the publisher and subscriber being in an
> inconsistent state, or having data consistency problems. In other words
they
> are out of sync.
> To resolve these problems you must verify that only the replication
process
> is making changes to the data that is replicated to the subscriber. In
other
> words are users directly making updates on the subscriber? If so, have you
a
> partitioing scheme that will ensure that these users will never update the
> data that is replicated from the publisher?
> You must also verify that you started off with a subscriber that was in
sync
> with the publisher. The best way to do this is 1) use the subscriber does
> not have the schema and data option when you deploy your subscription. 2)
if
> you are doing a no-sync subscription, ensure you run some data validation
> before you start up your distribution agent.
> I would advise you to run a data validation now, and see exactly how out
of
> sync you are. It could be 1 record, it could be many. Then perhaps
> reinitialize and start again. If you have scads of free disk space, run
> profiler on your subscriber to try to trap other users accessing the
> subscriber and determine what they are doing. If you can change all
> passwords on the subscriber, and see who comes to you complaining about
not
> being able to update data.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:O%236jnxiWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> procedure
>