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.