Sample Header Ad - 728x90

sql server replication issue

0 votes
1 answer
292 views
My SQL Server version is 2012 R2. I have one publishing machine with several publications, one distribution machine, and several subscription machines. Everything is working fine. However, when I try to add a new subscription on one of the publishers (The size of this database is close to 150 GB) and start the agent to create a snapshot, it gives me the following error message when it reaches 99%, Based on the information provided, there are some indications of query timeouts. I checked the setting: Property -> Connection -> Remote Query Timeout value 0,which means there is no limit for the duration of remote queries. plz advise, thanks all!
Error Message: 
Message: A transport-level error occurred when sending the request to the server. (provider: TCP Provider, error: 0 - The remote host forcibly closed an existing connection.) 
Command Text: sp_MSget_synctran_commands Parameters: @publication = xxxx_publish

Stack: 
at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command) 
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteReader(SqlCommand command, Int32 queryTimeout, CommandBehavior commandBehavior) 
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) 
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate) 
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotCommandManager.AddSyncTranCommands() 
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotCommandManager.AddSnapshotCommands() 
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.AddTransCommandsAndSetArticleTranIDTransaction(SqlConnection connection) 
at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen) 
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPostArticleFilesGenerationProcessing() 
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() 
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() 
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error Number: 10054) Get Help: http://help/10054  
Server xxxx, Level 20, State 0, Line 0 
A transport-level error occurred when sending the request to the server. (provider: TCP Provider, error: 0 - The remote host forcibly closed an existing connection.) (Source: MSSQLServer, Error Number: 10054)
Asked by Mr.spark (23 rep)
Oct 13, 2023, 12:49 AM
Last activity: Oct 13, 2023, 12:31 PM