sql server replication:
I have replication setup between server A & B, both SQL 2005. Server A
is distributor/publisher & B is subscriber. I have has merge
replication working for 10 databases for awhile now. Each database has
only one merge publication and one subscription each.
Symptoms: Publications starting show red X in replication monitor but
subscriptions still working. Publications fail when the default 14 day
schedule for the snapshot agent to kick in. Agent seems to get stuck
on step 2. After about 15 mins, agent does not fail or stops but
reports the following: "The replication agent has not logged a progress
message in %ld minutes. This might indicate an unresponsive agent or
high system activity. Verify that records are being replicated to the
destination and that connections to the Subscriber, Publisher, and
Distributor are still active." I have consulted SQL Books online about
MSSQL_ENG020554 with little success. I am able to create publication
object in the local publication folder, but the agent seems to hang
when I actually tries to create the database schema. The agent will
not create the folder in the /mssql/repldata/unc folder like the
original publications.
Troubleshooting steps I have done:
Verified that the process account (domain account) has write priveleges
to the repldata folder. Verified process account is also in the
Publication Access List.
Have gone into step2 of the agent job properties and modifed the T-SQL
to possibly create an error, but job just runs and runs as I view it in
the snapshot agent status from the publication. Verifed the
domain/login account being used is in good standing.
Tried output file with outputverboselevel but since step 2 doesn't seem
to actually process through, can get no output.
Subscriptions will not fail until I tried to reinitialize one and
required a new snapshot. Snapshot agent fails and now merge agent
fails for that publication. Nothing in the sql server, agent, or nt
logs to justify problem. I have two other domains that are mirrored
and working like a charm. Compared settings across all domains, but
have found no inconsistency.
When the merge snapshot agent executes and creates the schema, is it
using the domain/login account provided to connect from the distributor
--> publisher or does it use the distributor_admin account for this,
and if so, if I change it at the publisher/distributor server,
assumption says to change is at the subscriber?
Any advice will be more than welcomed.
--Tony
