SQLTeam.com | Weblogs | Forums

Replication fails when a certain number of publications are added


#1

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.


#2

Are you replicating from SQL Server to SQL Server? Is this transactional replication?

Which agent failed? The log reader? The distribution agent?

The error message implies it was the snapshot agent.


#3

Yes from 2014 SQL Server to 2016 SQL Server (compatibility mode set to 120).
We use a distribution server which is 2014 SQL Server.
What is failing is the snapshot agent.

As soon as I add more publications the snapshot agent fails. Then if I wait a while other snapshot agents from other publications start to fail. As soon as I delete some publications (not just the one I added but others to reduce the numbers) the snapshot agents start to recover.


#4

I'm sorry. I don't have any other thoughts that might help :frowning: