So I have a SQL server 2016 on a Windows Server 2012 instance. We have 140 databases(small to med). So the SQL instance was setup for self replication and in the process we have 140 SQL agent replication jobs. When the server reboots it can handle ~120 replication jobs, then when one is stopped it can not be re-started - it fails - but with no message at all - then says "between retries". In replication monitor - the agent has the red x and or is showing not running. In the event viewer I see logread.exe application crash. I was trying to research to see if there is a cap on the number of agent jobs running. Our memory is good, the disks and CPU usage is low. I have not been part of something of this size before and I am currently out of ideas. My current work around is to have a PowerShell script read the current failures and manually kick off the -Publisher [xxx] -PublisherDB [DbName] -Distributor [xxx] -DistributorSecurityMode 1 -Continuous . That is what makes me think there is a cap on the number of agents because I can run it with out the SQL-Agent. I was thinking maybe I need to change that to more of a scheduler but we have reports run that need timely info. What am I missing? Or am I right in looking to breaking up the job runs?
1 Answer
Weird. Never had that many agent jobs running replication before. Do you have a dedicated distributor, or is it all running form the publisher?
-
So everything is running from one dedicated SQL instance with publisher and distributor all on one instance. I am thinking about adding more publishers to handle the load - never seen this many replication jobs all on one server. Commented Oct 1, 2020 at 12:19
-
You may want to consider adding a dedicated distributor, if you have a standard 2012 license laying around. 2008 might even work in a pinch. We did and it took an incredible amount of load off of the publisher. Commented Oct 1, 2020 at 16:30
sp_addpublicationcode and the value of@logreader_job_nameparameter. If it is passing a name, it will create a new one. learn.microsoft.com/en-us/sql/relational-databases/…