Transactional replication not replicating with 'The process is running and is waiting for a response from the server' error after adding new article

Sowjanya Gopal 20 Reputation points
2023-09-05T13:38:51.3733333+00:00

Hi

I have a Transactional Replication setup which is working fine since 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 & then started the snapshot agent. 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 same error since last 3 hours. Undistributed commands is increasing every minute. There are no blockings on Pub or Subs.

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

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

Kindly let me know how to fix this issue.

Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,997 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 88,161 Reputation points
    2023-09-05T21:21:53.1866667+00:00

    2023-09-05 11:40:54.266 The process is running and is waiting for a response from the server. It is showing same error since last 3 hours.

    Strictly speaking, that does not look like an error to me. It is just an informational message.

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

    ASYNC_NETWORK_IO is not an error, but it is a wait type. I am little surprised to see this wait type with BULK INSERT, though. Normally, you see this wait type if there is a SELECT statement returning many rows, and the network or the client is slow in slow in transferring/consuming these rows. That is, SQL Server is waiting for rows being cleared from its output buffer and is prey to external circumstances beyond its control.

    Exactly what is the issue here I don't know, but maybe it is waiting for BCP to feed more rows. In any case, it is an issue outside SQL Server. Maybe you have saturated some network resource with those 10 GB.

    Since this operation is blocking the rest of replication to work, maybe the best is to cancel the operation. Although, how do that I don't not know - I don't work much with replication myself.

    0 comments