sql server replication: When I restart the distribution agent for some reasons
(e.g. rebooting the machine) it fails to start properly
generating errors "Unable to insert duplicate records..."

To clarify the setup:

We have Publisher (A) and Distributor-Subscriber (B).
The publication is transactional not distributing changes
at the subscriber.

The Publisher database is relatively heavily loaded (10s of
inserts/updates a minute).

When I stop the Distribution Agent, then after some time start
it back it stops with error "Unable to insert duplicate records...".
Looks like it tries to re-distribute the transactions it has already
distributed.

I have to delete manually all the "duplicate" records to make the
agent running.

What do I need to do to be able to shut down Distribution agent

[quoted text, click to view]

I'm not sure why would I need to rebuild a snapshot considering that:
- this replication has been created long ago
- the Publisher database is being cleaned up regularly and we don't replicate
deleted records so Subscriber works as an archive and it contains much
more data then the Publisher

Also I'd rather not mask the Distribution agent errors because in this case
I also mask potential real problems.

I have two more related questions:

- does distribution agent stop immediately if I stop LogReader agent?
- what is the distribution agent's latency? Should I stop log reader first,
then wait for some time and stop distribution agent so I can safely reboot
the machine?

Hi:
I do not know why it cause, but I'd like to give my opnion as a
considuration:
1. You may rebulid you shapshot to see if this happen again.
2. You can change the Distribution Agent configuration and made it skip
error or SkipFailureLevel, after all it does noyt take bad affect of your
database.
Best Wishes
Wei Ci Zhou

And Please see this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;323545&Product=sql2k

Hi,

If you are not propagating deletes to the subscriber, it's very possible
that you are getting duplicate records. Here is an example. If you have a
record with an identity column value of 1 and that record is deleted on the
publisher, it won't be deleted on the subscriber due to the way replication
is configured. Then if you insert another record on the publisher with an
identity column value of 1 for the same table, the distribution agent will
fail because there is already a row containing the value of 1 on the
subscriber.

If you are manually deleting rows to get around this problem, that tells me
you are removing transactions from the distribution database. That can and
will cause inconsistent data between the publisher and subscriber so that
the databases are not synchronized with each other.

The distribution agent does not stop if you stop the log reader agent. The
latency of the distribution agent depends on the number of transactions
that must be delivered to the subscribers. If SQL Server is stopped before
the machine is rebooted, the agents will stop running. After the reboot,
they should restart without losing any transactions. It is not necessary
to specifically stop the agents before rebooting.

Regards,
Shirley
SQL Server Support

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.