Transactional Replication: can create 52 publication, but creating 53rd leads to problems

Question

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:

1. Enable replication for a database (if not enabled already):

2. Create logreader agent for a database

3. Create publication for a database (if doesn't exist)

4. Create snapshot agent for a database (don't run it yet)

5. Add articles to the publication

6. Run snapshot agent job (and wait for it to finish)

7. Create subscription database (if it doesn't exist)

8. Add subscription in the subscription database (if it doesn't exist)

9. 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 see the SQL Server Agent job history for job ‘……’". In the details section it says: "The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information". Next I go to the SQL Agent Jobs and review the output. In the job history the failed step says: "Message : The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information". In turn, the previous step has no info at all. Just the word “Message” with nothing after that. I have also reviewed the windows application log looking for any errors, but it simply repeats the same messages I see in the replication monitor and agent job history. I added “–output” parameter to the agent command (in the SQL Agent Job step properties) hoping that the output file would provide me with more information, but no output file gets created, which leads me to believe that the agents don’t even start.

I was thinking that I might have hit some hardware limit, so I checked the disk space and there is plenty of it available. I have also checked RAM and it's not even 50% used.

Finally, what really gets me is that I am able to run all of the agents manually from the command line. For example, I can copy paste parameters from the SQL Agent snapshot job and run snapshot.exe from the command line with these parameters – the snapshot gets created successfully. The same goes for logreader and distribution agents.

I am completely confused here and appreciate any pointers in the right direction.

Final note (and maybe the only silver lining): The problem is fully re-producible. I can successfully setup my 50+ subscriptions and then the issues will always start around subscription number 52-53.

Thank you!

Answer 1

I believe you are running into the desktop heap issue described here (support.microsoft.com/kb/949296) and here (support.microsoft.com/kb/824422).