one conflict > all other replication hangs

hi all

let's say i have three publications running on a network of one central server and four shops.

- a merge replication that keeps the price-table up to date between all shops and the central server

- a transactional replication of the arrived_orders-table from the central server to the shop in case

- a transactional replication of the sold_products-table fromt he shops to the central server.

the whole replication network runs on a distribution database that is located on the central server.

now, i've noticed already a few times, when a conflict occurs in one of the replications, also all other replications stop working. (for example when the transactional replication can not insert a line in the sold_products table because of a violation against a UNIQUE constraint in the central server).

Is it normal that also the merge replication and the other transactional replication don't do their inserts, updates etc any more?

Thanks

Different replications uses different instances of replication agent (logreader, distribution and merge). Other replication agents should continue when one agent fails. Does other agents run at all, or they are having failures also?

Peng

|||@.peng song
they don't use the same distribution agent, isn't it?
i configured only once my distribution database on my central server.... only once (!).... and you say that they use other distribution agent?

can someone explain this?
|||

although you may use one distribution database, there are different distribution/merge agent processes running for each of the replication. So you need to take a look at the error message for the other agent jobs and do the troubleshooting from there.

Peng

|||@.peng song: and about a log reader agent?
if several publications exist of one and the same table of a database, then it uses the same log reader agent or does it uses for each publication another instance?

this comes into my mind because in the scripts i have to install, i have one script that has to be installed in advance:

use [DBX]
exec sp_replicationdboption @.dbname = N'dbx', @.optname = N'publish', @.value = N'true'
GO
use [DBX]
exec [dbx].sys.sp_addlogreader_agent @.job_login = N'pcn\login', @.job_password = pwx, @.publisher_security_mode = 0, @.publisher_login = N'login', @.publisher_password = N'pwx', @.job_name = null
GO

this is the only place in my scripts that i have to install, where i see the sp_addlogreader_agent.... this is slightly in contradiction to what you say above, that for each replication another instance is created.

am i right or am i wrong?
(it's also possible that the scripts that i received are wrong of course)
|||i found maybe the problem.
it was the log reader agent that failed at all transactional replications.

there is still a question:
is there one log reader agent per publication, or one log reader agent per table (on which replication is installed) ?

(so if i have multiple transactional publications of one table, is there one log reader agent or multiple?)
|||

There is one logreader agent for each published database. So if there are multiple publications on a single database, there is only one log reader.

Your origional post indicates that your two transactional publications are on different server. So I assume they are using different agents.

What error message do you see.

Peng

|||

the problem was only in the second point of my original post.

(i found after investigation that the merge replication and the third replication of my original post still continued to do their job fine).

the problem was only in the publications of the deliveredOrders-table on the central server.

for specific reasons i created a transactional publication of the deliveredorders-table on the central server towards each shop (so a filtered publication for each shop).

the error i got was this: "The process could not execute 'sp_replcmds" for all publications of that type.

All publications failed.

my solution was: i recreated all publications, and now it works fine again since ten days ago.

the problem was probably that i exported one script to install one publication of that type, and for all other publications i copy-pasted the file and just updated manually the parameters. probably something was badly changed while manually changing the installation scripts.

sql