When Good SQL Server Replication Agents Go Bad…
Originally published on KendalVanDyke.com.
Here’s the scenario: You’re responsible for replication (you are the DBA after all) and all of your publishers use a dedicated distributor. Now this happens to be in a high traffic datacenter and there are a lot of publications and subscriptions which means there are a lot of SQL Server replication agents running (think hundreds). One day you’re working on spinning up a new server and subscribe it to the handful of publications it needs..and then you start noticing that some distribution agents don’t run anymore. No matter what you do – restarting agents, restarting services, or restarting the server – seems to fix the problem. You wonder if your distributor has “hit its limit” and if you need to start talking to your boss about buying new hardware. Meanwhile you’re in firefight mode because subscribers aren’t getting data, customers are starting to notice that things aren’t updating, and you’re pulling your hair out wondering how to fix it.
If you’ve ever been in this scenario, chances are the new distribution agents have caused your distributor to use up its non-interactive desktop heap. Fortunately there’s an easy workaround to get things back on track. Microsoft KB article 949296 details the problem and links to KB article 824422 for the workaround. It requires editing the registry so you’ll need admin level access to the server. Once in place your distribution agents should all run again and you can tell your boss about how you just prevented the company from having to spend a few thousand dollars on a new distributor.
About the Author
Kendal Van Dyke
UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.
Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.
Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.
Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at http://www.kendalvandyke.com/.
Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke https://upsearch.com/kendal-van-dyke/.
About UpSearch
UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.