I am a SQL DBA and I am having an issue with replication. We have a large SQL server with over 400 databases along with a distribution server. I am building a new server to offload some of the processes.
I want to replicate about 12 tables from each of theses databases. When I build the publication with the 12 tables or articles it creates the subscription with no problem.
However, when I hit about the 25th publication I start getting errors that the agent has failed. Not only on the new publication but the current ones that are running fine. Once I delete
some of the publications the errors go away.
The only errors I see in the logs and the jobs are:
The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.
Executed as user: domain\sql_agent. Agent shutdown. For more information, see the SQL Server Agent job history for job '???'. [SQLSTATE 01000] (Message 20557)
Replication-Replication Snapshot Subsystem: agent ???Pub failed.
The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed. [SQLSTATE 42000] (Error 14151). The step failed.
I have checked memory on the distributor and it is about 30%
I know there is an unlimited number of publications that can be added however, we can't get past this certain threshold of about 20-25.
