Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
2  
Could you be running into the desktop heap issue described here (support.microsoft.com/kb/949296) and here (support.microsoft.com/kb/824422)? –  Brandon Williams Dec 30 '13 at 4:17
1  
can you put information what version of Windows Server and Sql Server you are working on ? –  Kuba Miazek Dec 30 '13 at 12:34
1  
@Brandon, you are a life saver! This was exactly the issue, which was causing this. Increasing the "SharedSection" parameter value as desribed in KB824422 solved it. Thank you very much! Note that while the article refers to WindowsNT and SQL Server 7.0/2000, it certainly applies to the latest versions as well. In my case I am running Windows 2008R2 Server and SQL Server 2012. –  Misha_SQL Dec 30 '13 at 20:54
add comment

1 Answer

up vote 4 down vote accepted

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

share|improve this answer
1  
Thank you once again! This was it! –  Misha_SQL Dec 30 '13 at 21:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.