2

I have a Transactional Replication setup which has been working fine for a long time. It is a Pull Subscription. I got a new requirement to replicate a large article which is 10GB.

allow_anonymous & immediate_sync are set to false by default.

I added the new table from GUI and then started the snapshot agent. The initial snapshot got created successfully.

However, it is not replicating from distributor to subscriber with below error message.

2023-09-05 11:30:53.835 Bulk copying data into table 'xxxx'

2023-09-05 11:35:54.055 The process is running and is waiting for a response from the server.

2023-09-05 11:40:54.266 The process is running and is waiting for a response from the server.

It is showing the same error for the last 3 hours. The undistributed commands are increasing every minute. There are no blockings on Publisher or Subscriber.

However, I see that the Bulk Insert SPID related to Distribution agent is waiting with wait type Async_Network_IO error.

Please note that it has stopped replicating all other tables as well.

Kindly let me know how to fix this issue.

4
  • Hi, and welcome to dba.se! Please tag your question with the relevant server.
    – Vérace
    Sep 5, 2023 at 13:46
  • 1
    Could you test copying initial load file to subscriber to exclude low network latency?
    – SergeyA
    Sep 5, 2023 at 18:56
  • 1
    Guess it is doing it’s work but network bandwidth is not that great. To unblock yourself you can try removing the article from replication for now. Copy all rows using Import/Export wizard from SSMS - yeah it could take a bit considering article size but this will not interrupt replication process. And once that is done, you can sync missing records once making sure no transactions are incoming at publisher during this period. Once all rows are in sync - add that article to replication and skip snapshot creation part. Sep 7, 2023 at 16:48
  • Thank you SergeyA & Gaurav for your inputs. But, we cannot afford to make sure there are no incoming transactions at publisher during the period of time. Is there any alternate method to achieve this. Sep 8, 2023 at 6:58

0

Your Answer

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