0

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)

1 Answer 1

1

I don't believe you have a query timeout due to settings. Out of the box, I've had Publications that took hours to sync to the Subscribers and never timed out.

Rather it sounds like the server where this new Subscriber lives has an unstable connection between it and the source Publisher server. Maybe the connection is spotty with however the network is setup. Unfortunately I think you have an infrastructure problem either way, not a database one.

One thing that may help you is trying to initialize the Subscriber from a backup. I believe you can have the backup file local to the Subscriber server and initialize it locally. That eliminates most infrastructure problems. Then it shouldn't take long for the Publisher to be in sync with the Subscriber.

13
  • No data has been transferred to the new subscription server, the distribution agent snapshot has not been completed. The new subscription servers are all within the local area network, and there are no abnormalities in network monitoring. I also don't think it's a query timeout issue. The published database has 213 tables and is over 150G. I did a test where I only published a few tables, and the snapshot was successfully created and the subscription completed. I've tried several times, if i publish all database, each time the snapshot progress reaches 99% and then fails.
    – Mr.spark
    Commented Oct 13, 2023 at 13:07
  • I wonder if SQL Server has any parameters for concurrency/data size? I tested it, and publishing 160 tables/100G size, the subscription can also be successful.
    – Mr.spark
    Commented Oct 13, 2023 at 13:19
  • @Mr.spark I'm pretty sure you're running into an infrastructure issue. Publishing 100 GB of data shouldn't be that much different than 150 GB, but perhaps enough to show that it's losing connection during the process. I wouldn't doubt if you tried publishing all tables 100 times, it would succeed at least once at some point. That, or you're running into a weird bug with something odd about one of your tables - but I find this less likely if you're already publishing these tables to other Subscribers.
    – J.D.
    Commented Oct 13, 2023 at 14:00
  • 1
    Thank you for your reply. I will test a few more times with fewer tables published to see what happens.
    – Mr.spark
    Commented Oct 14, 2023 at 0:11
  • 1
    Yesterday, I published a database of 150 gigabytes without encountering the aforementioned issue. At present, I perceive that the adjustment of parameters has yielded positive effects.
    – Mr.spark
    Commented Dec 21, 2023 at 1:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.