sql server replication: Trans replication becoming out-of-synch -- why? -- sql server replication

Trans replication becoming out-of-synch -- why?

Eric H
3/4/2005 3:33:03 PM

sql server replication:

We have recently implemented a relatively simple replication scenario, which
appeared to be fully functional. Unfortunately, we discovered today that the
subscriber is somehow missing or losing some commands, causing the two DBs to
become out of synch.

I would appreaciate any pointers you can give me as to possible causes for
this. FYI, I don't have a great deal of experience with replication, so I
may have made some sort of newbie configuration error.

Replication Scenario:
2 high-horsepower SQL2000 Enterprise Edition servers, one a production DB
serving as publisher, the second (subscriber) serving as a read-only server
for an ASP.NET web client. Transactional replication on many but not all
tables, single direction, with a secondary bi-directional transactional
scenario on 3 unrelated tables. Concurrency risk in the bi-directional
scenario is minimal due to business rules. The production DB is updated from
an application using ADO recordsets.

Problem Summary:
The distributor gets Replication Error #20598 ("The row was not found at the
subscriber when applying the replicated command.") The command is:
"{CALL sp_MSupd_DiseaseReviewDetails
(NULL,NULL,NULL,'0',NULL,{89B5FDF6-B17A-47E1-9BC7-19941E36149F},0x08)}"
On looking at the tables, we find that the relevant record for the update
does not exist on the subscriber DB, nor does the parent record. Both do
exist in the publication DB. Manually calling the sp_MSins commands for the
parent and child tables allows the distribution agent to move on to the next
error (which is identical, but for a different key).

Any thoughts as to how the subscriber may have become out of synch, or why
the INSERT commands may not have been replicated when these items were
created on the publisher?

Re: Trans replication becoming out-of-synch -- why?

Hilary Cotter
3/6/2005 2:38:36 PM

verify that you are not using the skiperrors parameter in your distribution
agent. Then verify that you are not using the continue on data consistency
errors profile.

Finally run a validation to detemine which tables are out of sync and how
serious the degree of lack of synchronicity is.

Then you will have to determine how to fix this.

The reasons that you will get this is normally due to someone or some
process making updates directly on the subscriber.

Also verify that you are enforcing check constraints for replication and you
are not doing any cascading updates or deletes.

[quoted text, click to view]

Re: Trans replication becoming out-of-synch -- why?

Eric H
3/7/2005 5:19:02 AM

Thanks for the response, Hilary.

I can confirm that SkipErrors / Continue on Dist Errors are not on (also
note that the error message mentioned below came from the Distribution Agent).

The parent table is missing ~600 records, the child table ~16,000 records
(it's key-value, so this isn't quite as bad as it sounds). All of these are
recently added records in the parent tables.

I plan to "fix" the problem temporarily by re-initializing the replication.
However, the client won't be satistfied with that as a long-term solution, so
I'd like to track down the cause of the missing inserts.

I also plan to page through the list of pending changes that was generated
by sp_browsereplcmds, to see if the inserts are in the log in the first place.

I feel confident that no-one is making changes directly on the subscriber,
but I'll go through the list of the SQL Agent jobs just in case. SqlService
(the SQL Agent account) and sa are the only users who should have permission
to modify the subscriber database, excepting the 3 unrealated tables
mentioned previously. We have no cascading updates or deletes.

One thing I realize that I forgot to mention in my earlier description (in
case it matters) is that we have a vertical filter on the replication, in
that we are not replicating any Text or Image columns.

Is there anything else that I should monitor to determine when these inserts
are being lost?

Thanks again for your help.

[quoted text, click to view]

Re: Trans replication becoming out-of-synch -- why?

Eric H
3/7/2005 7:09:02 AM

Just to follow up, I have tracked this a little further through the audit
process we have in place for the production DB. It looks like the missing
records were inserted into the production table right around the time
replication was set up. At this point I suspect some sort of timing issue in
my creation process.

I'll follow up on whether the problem re-appears once we have a chance to
re-initialize the replication.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.