snippetsqlMinor
Transactional Replication: can create 52 publication, but creating 53rd leads to problems
Viewed 0 times
cancreatetransactionalcreatingbutreplication53rdpublicationproblemsleads
Problem
My apologies in advance for a long post, but I was really trying to make sure I do my due diligence before turning to the community for help, so here it goes.
I am working on a proof of concept with the purpose of using transactional replication to maintain a "reporting" copy of our databases. The setup will include a "Publisher" server and a separate "Subscriber" server. The replication will be a "Pull" transactional replication with a distributor running on the Subscriber server. Once again: Publisher => pull => (Distributor + Subscriber)
We expect to have a lot of databases on each of our servers, so, in order to mimic this scenario, I restored 100 databases (about 1 GB each) on the publisher. All of the databases are identical.
I wrote a script, which enables me to setup replication in an automated fashion (since obviously I don't want to do it manually dozens of times). The script performs the following steps:
-
Enable replication for a database (if not enabled already):
-
Create logreader agent for a database
-
Create publication for a database (if doesn't exist)
-
Create snapshot agent for a database (don't run it yet)
-
Add articles to the publication
-
Run snapshot agent job (and wait for it to finish)
-
Create subscription database (if it doesn't exist)
-
Add subscription in the subscription database (if it doesn't exist)
-
Create distribution agent
I am able to successfully setup replication for about 52 of my databases and everything is running fine. When I get to database #53 things start to go wrong. The snapshot creation is successful, but the logreader and the distribution agents fail. Moving on to database #54 everything is now completely broken. The snapshot does not get created and none of the agents are running. I have carefully reviewed replication monitor and SQL Agent jobs looking for error messages, but I didn't find anything helpful. The replication monitor simply says: "Agent Shutdown. For more information
I am working on a proof of concept with the purpose of using transactional replication to maintain a "reporting" copy of our databases. The setup will include a "Publisher" server and a separate "Subscriber" server. The replication will be a "Pull" transactional replication with a distributor running on the Subscriber server. Once again: Publisher => pull => (Distributor + Subscriber)
We expect to have a lot of databases on each of our servers, so, in order to mimic this scenario, I restored 100 databases (about 1 GB each) on the publisher. All of the databases are identical.
I wrote a script, which enables me to setup replication in an automated fashion (since obviously I don't want to do it manually dozens of times). The script performs the following steps:
-
Enable replication for a database (if not enabled already):
-
Create logreader agent for a database
-
Create publication for a database (if doesn't exist)
-
Create snapshot agent for a database (don't run it yet)
-
Add articles to the publication
-
Run snapshot agent job (and wait for it to finish)
-
Create subscription database (if it doesn't exist)
-
Add subscription in the subscription database (if it doesn't exist)
-
Create distribution agent
I am able to successfully setup replication for about 52 of my databases and everything is running fine. When I get to database #53 things start to go wrong. The snapshot creation is successful, but the logreader and the distribution agents fail. Moving on to database #54 everything is now completely broken. The snapshot does not get created and none of the agents are running. I have carefully reviewed replication monitor and SQL Agent jobs looking for error messages, but I didn't find anything helpful. The replication monitor simply says: "Agent Shutdown. For more information
Solution
I believe you are running into the desktop heap issue described here (support.microsoft.com/kb/949296) and here (support.microsoft.com/kb/824422).
Context
StackExchange Database Administrators Q#55795, answer score: 5
Revisions (0)
No revisions yet.