May 3, 2006 at 4:13 am
We run a large number of publications and subscribers for load balancing of heavily accessed data across servers. We use a separate distributor with processing power to do this. However we have recentlycome across a problem where there seems to be a limit to the number of distribution/snapshot agents that can be running. This initially looked like a worker threads problem so we increased this but it had no impact. Agents go through step one ('starting agent' message) but then appear to just hang. I've profiled the distributor and there is no activity there at all while the agents are hung. There are no locks anywhere.
I'm wondering if there's a limit on the number of remote server connections allowed at one time but have found no info on this.
We currently have 112 distributor processes running and 4 logreader processes along with the periodic housekeeping agents. Any further agent added just hangs unless I stop one of the others.
Would be interested in any ideas anyone has on this.
May 3, 2006 at 1:02 pm
The only limit I've run into, has been in the distribution database being too busy to keep up.......the replcommands table gradually gets larger and larger, and isn't able to ever catch up to the replication going on......
We split the replication between multiple distribution databases, and so far have been fine......
May 4, 2006 at 1:39 am
Thanks for that. Could give an approach to a different problem we get occasionally where at some peak times we get bigger latency on our logreader agent - looks like contention on the distribution db. I had been hoping for multiple distributors in 2005 but no such luck. Hadn't come across multiple distribution dbs so hadn't realised it was an option. Must have missed that page in the manual. I think there's a good chance it would solve our logreader latency problem. I better do some reading. Again, many thanks.
further question - how many concurrent agents are you running with on your distributor? Guess the limit could be linked to distribution db connections in some way.
July 23, 2006 at 10:13 pm
Just in case you haven't solved this yet. There is a limit of 100 worker threads in SQL Server Agent for distribution jobs.
There is a registry setting that can be edited to increase this. See the MS KB Article Q306457
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply