0

I am experiencing some issue with my subscription (transactional replication) recently as whenever I tried to add a new subscription to my existing publication either way I use Initialize when Immediately or At first synchronization I always encountered issue resulting unsuccessful subscription.

When I select initialize when Immediately, after I hit the finish button, I encountered this error:

Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Command Text: sp_MSreplupdateschema Parameters: @object_name = [dbo].[myTableName]

Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command) at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout) at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout) at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ConcurrentPreArticleFilesGenerationTransaction(SqlConnection connection) at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen) at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPreArticleFilesGenerationProcessing() at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoPreArticleFilesGenerationProcessing() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: -2) Get help: http://help/-2 Server MyServerName, Level 11, State 0, Procedure , Line 0 Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Source: MSSQLServer, Error number: -2) Get help: http://help/-2 Source: Target Site: Message: The wait operation timed out Stack: (Source: , Error number: 0) Get help: http://help/0

enter image description here

However if I select Initialize when At First Synchronization, it gives me the status of The initial snapshot for article myTableName is not yet available.

enter image description here

Furthermore, if I reinitialize the subscription nothing happens

enter image description here

Note:

I am using SQL Server Standard 2019 Edition, Microsoft SQL Server Management Studio 2019

Here's also my Server specification: HP Proliant Gen10 +

enter image description here

and my current system utilization via task manager

enter image description here

5
  • I tried to use dbcc opentran to search and kill for spid who is responsible for blocking but it didn't help, I tried also the other suggestion to point the new subscription to new agent profile, however it wont help also. Oct 22, 2023 at 20:04
  • I also tried to reboot the server, yet problem still persist, also my existing publication had 167 subscribers already which works fine, until recently when Im trying to add more subscription suddenly crossed that kind of weird subscription issue. Oct 22, 2023 at 20:21
  • Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64) Apr 29 2022 18:00:13 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) @DanCo Oct 23, 2023 at 5:40
  • as of the moment, there are 1,203,511 transactions on my publication. I use only 1 article (1 table), sorry my bad it's not 167, it's only 67 current subscibers so far @J.D. Oct 23, 2023 at 5:45
  • @Caspersky12 That's better, but 67 is still a lot TBH. What's the size of the table that's being replicated? How frequently are changes made it to?
    – J.D.
    Oct 23, 2023 at 12:14

1 Answer 1

1

Your SQL Server is currently more than 20 patches behind. Consider upgrading at least to CU19. I had an experience where transactional replication wouldn't run (throwing some errors) until we installed the latest patch. Check this link for SQL Server 2019 build.

However if I select Initialize when At First Synchronization, it gives me the status of The initial snapshot for article myTableName is not yet available.

That is the correct behavior.

Follow the steps below:

  1. open the Replication Monitor.
  2. click on the publication in question.
  3. go to the Agents tab.
  4. look for the status of the snapshot agent, which should be "Never started."
  5. right click on it and select View Details.
  6. snapshot window will appear.
  7. click on Action.
  8. click Start Agent. This action will initiate the snapshot agent.
  9. the snapshot agent will begin operations such as bulk copying, creating BCP, locking tables, and more.
  10. once the process is complete, navigate to the All Subscription tab.
  11. check to see if the distribution job has started sending commands. "Refresh" multiple times.

Furthermore, if I reinitialize the subscription nothing happens

You need to start the snapshot job. see above comment.

Additional checks you need to do:

  1. you need to check the replication jobs with the REPL- category prefix to see if the job owner is set correctly. The job should be under either sa or distributor_admin, or it will fail.
  2. check snapshot folder permission
  3. check the transaction retention under distributor properties. This setting will depend on your business requirements. However, I usually set it to At least: 72hrs and But not more than: 72hrs. I apply the same 72hrs setting for history retention.
  4. since you are already behind by millions of commands and depending on your network latency, you might consider optimizing your distribution job profile. This is the profile I set if i need optimization:
    • CommitBatchSize 1000
    • CommitBatchThreshold 10000
    • Polling Interval 1
    • Query Timeout 9600
  5. on subscriber instance, I usually set Max Text Replication Size to -1

This is not a comprehensive list, but it's a good starting point for transactional replication configuration.

5
  • Thank you @Dan Co, just wanted to clarify the ff: 1. If I update my sql server build based on your suggestion, does it also mean I need to update my Windows Server Standard build edition too? furthermore, is there any downtime if I do the update? I mean that server is live and running for production. 2. Follow the steps below: open the Replication Monitor, etc, if I start the agent that's the time that I encountered the execution timeout expired. Otherwise, do I need to update first the sql server build before executing the steps you have mentioned. thank you. Oct 23, 2023 at 16:18
  • @Caspersky12, first address your replication issue by running the snapshot job. As for updating SQL Server to the latest CU, you should plan this carefully. Test the update in a lower environment before applying patches to production. Unfortunately, applying a patch requires downtime. Only proceed with the patch if, after testing, replication remains non-functional. Schedule the necessary downtime for the patching. And to clarify, there's no need to update Windows when updating to the latest SQL Server CU.
    – Dan Co
    Oct 24, 2023 at 0:29
  • for the query timeout, you need to create a new profile for distribution job. follow step 4 and 5. these config doesn't require any downtime. How many pending commands now?
    – Dan Co
    Oct 24, 2023 at 0:32
  • one last thing, if I start the Agent, does it mean that all existing subscibers should also initiate a bulk copying? i'm referring to my existing 67 subscribers which has no issue so far. See screenshot below which I put / mark color green on subscribers that had no issue, while the x mark (in color red) was the subscriber with Request time out issue. i.stack.imgur.com/XpcRF.png From what I understand, this is the one you want me to start the agent i.stack.imgur.com/BDPwN.png Oct 28, 2023 at 15:32
  • It should bulk copy only for the new subscriber that you added. The existing subscriber will still receive a message like x transaction(s) with x command(s) were delivered. Yes, the second screenshot shows the snapshot agent. When you run the snapshot agent, it will lock the publisher database, so be careful. Once the snapshot agent is complete, you will see the commands start flowing on the distribution agent.
    – Dan Co
    Oct 30, 2023 at 3:10

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.