20598 MSrepl_errors information required

I'd like to know how to know what replication stored procedure and parameters
to that is being run for a 20598 error I'm receiving.
in MSrepl_errors, for all these 20598 errors
error_text is: "The row was not found at the Subscriber when applying the
replicated command."
xact_seqno is: 0x0000... (ZERO)
so there seems to be no chance to use sp_browsereplcmds <xact_seqno>
My configuration: Transactional "nosync" replication
SQL Server 2000 SP4
The Distribution Agent Profile has the following:
-SkipError 2601:2627:20598
This is due to the fact my Publisher has a 2 year history of data and we're
replicating new data to a fresh DB . Deletes/Updates are not found, hence
the 20598 errors.
However, I'd like to know what data is erroring out. I will likely be able
to reduce these errors to a minimum but don't have any information on them to
investigate.
Suggestions?
Jack Griffith
Enable logging as per this kb article.
http://support.microsoft.com/kb/312292
Note that it appears that you are trying to update a row on the subscriber
which has already been deleted.
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
"Jack Griffith" <JackGriffith@.discussions.microsoft.com> wrote in message
news:188524CC-3706-4D41-B916-319D5C0715F0@.microsoft.com...
> I'd like to know how to know what replication stored procedure and
> parameters
> to that is being run for a 20598 error I'm receiving.
>
> in MSrepl_errors, for all these 20598 errors
> error_text is: "The row was not found at the Subscriber when applying the
> replicated command."
> xact_seqno is: 0x0000... (ZERO)
> so there seems to be no chance to use sp_browsereplcmds <xact_seqno>
> My configuration: Transactional "nosync" replication
> SQL Server 2000 SP4
> The Distribution Agent Profile has the following:
> -SkipError 2601:2627:20598
> This is due to the fact my Publisher has a 2 year history of data and
> we're
> replicating new data to a fresh DB . Deletes/Updates are not found, hence
> the 20598 errors.
> However, I'd like to know what data is erroring out. I will likely be
> able
> to reduce these errors to a minimum but don't have any information on them
> to
> investigate.
> Suggestions?
> --
> Jack Griffith
|||Updating a row that has been deleted is indicated by the xact_seqno being 0x00?
Jack Griffith
"Hilary Cotter" wrote:

> Enable logging as per this kb article.
> http://support.microsoft.com/kb/312292
> Note that it appears that you are trying to update a row on the subscriber
> which has already been deleted.
>
> --
> 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
>
> "Jack Griffith" <JackGriffith@.discussions.microsoft.com> wrote in message
> news:188524CC-3706-4D41-B916-319D5C0715F0@.microsoft.com...
>
>
|||- First of all. Thank you! I really appreciate your quick response to my
initial request. I applied the suggestion from the knowledgebase article,
heeding the warning regarding the impact of logging.
- The net result was that I was able to identify and resolve one of the
sources of my errors which reduced about 90% of those occuring. I'm left
with 10% unsolved and no practical way to identify them.
==> My question is:
Why is the xact_seqno in MSrepl_errors = 0x00 when it should be ( I think!):
0x0004c6c4000006570007
If MSrepl_errors could the text of the error, and xact_seqno, it would take
huge burdon off, as I cannot afford to run the logging given the onerous load.
More details are below to explain this question.
- The impact of error logging as per your suggestion was huge! In one hour,
my log filled up with about 50Mb's and the transaction latency went from
1,500 ms to 1,500,000ms.
- As soon as I started to log the activity, there were no errors. I was
thinking it was a classic "Heisenberg uncertainty principle" situation, but
in the last couple of minutes of the hour, I got 2 errors.
- Had to stop logging at that point. I reset the logging level to '0' which
should log errors only. However, it didn't seem to do that.
- I inspected the error logs and was a bit misled. You have to be careful
to group all of the error activity together in spite of the other unrelated
transaction activity that is interleaved. It's somewhat logical but
disorienting.
- OK the two errors were attributed to UPDATE statements to a table for
which the rows are non-existent in the Subscriber side. It was not
unexpected in this case.
- What is unexpected, is the inability of the MSrepl_errors table to track
the information. I say this because I find the following information in the
log:
============= start of error information =================
Last transaction timestamp: 0x0004c6c400000656000200000000
Transaction seqno: 0x0004c6c4000006570007
--^^^^^^^^^^^^^^^^^
Command Id: 1
Partial: 0
Type: 0
Command: [4/1/2007 6:42:46 PM]<SQL Server name>.<DB Name>:
{...several CALL sp_MSins<no problem table>...}
{CALL to sp_MSupd<culprit table> }
^^^^^^^^^^^^^^^^^^^^^^^^
{...several CALL sp_MSins<no problem table>...}
Skipped 1 error(s) when applying transactions at the Subscriber.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[4/1/2007 6:42:46 PM]<SQL Server name>.<DB Name>: update
MSreplication_subscriptions set transaction_timestamp = ?, "time" = ? where
publisher = ? and publisher_db = ? and independent_agent = 0 and
subscription_type = 0
Last transaction timestamp: 0x0004c6c400000657000700000000
Transaction seqno: 0x0004c6c4000006590009
Command Id: 1
Partial: 0
Type: 0
Command: [4/1/2007 6:42:46 PM]<SQL Server name>.distribution: {call
sp_MSadd_distribution_history(13, 3, ?, ?, 92518, 92518, 0.00, 0x01, 1, ?,
0, 0x01, 0x00)}
The commit timestamp value is: 0x0004c6c400000654000600000000
if exists (select * from sysobjects where name = 'syspublications') if
exists( select * from syspublications where repl_freq = 0) exec
sp_replsetoriginator ?, ?
ErrorId = 974, SourceTypeId = 5
ErrorCode = '20598'
ErrorText = 'The row was not found at the Subscriber when applying the
replicated command.'
[4/1/2007 6:42:46 PM]<SQL Server name>.distribution: {call
sp_MSadd_repl_error(974, 0, 5, ?, N'20598', ?)}
=================== end of error stream ====================
- So to repeat: why is the xact_seqno in MSrepl_errors = 0x00 when it
should be ( I think!): 0x0004c6c4000006570007
Bug?
Jack Griffith