Fix Replication process is running and waiting for a response from the server

Fixing SQL Server ReplicationThe past weekend, I had a pager from a client complaining that their replication is stuck and they need help to fix the replication. Earlier that day, client has kind of issue with replication and they had did fail over but that did not helped them with replication. On top of that they had a new error this time, it was link server that started complaining. Without further ado, I will now share how did I Fix Replication process is running and waiting for a response from the server error message.

 

When I had a call from client, and joined the bridge client has shared a screen with me since I do not have the login credential for that client yet. What I have observed first was about the linked server, it says “The test connection to the linked server failed“. This error is self explaining actually, and as per this error I did ping and telnet to the server which was working fine as obviously. The next check was to review the configuration and the property of the link server. For some reason, I see that the Data Access property of link server is not configured/false instead of having True as the value. As soon as I fixed Data Access properly of link server both servers – the Publisher and the Subscriber started talking to each other fine within replication.

However, the Replication was still not working, it is still stuck saying The process is running and is waiting for response from the server. Looking at this error I have asked customer if they have already checked for the blocking process, they told me that they have checked already and there wasn’t any blocking. I have suggested them stop and restart the Distributor Agent which did not helped obviously, the same error prevailed. Asked them to recheck the blocking and this time, we have found that there was a report that was running from previous night and is blocking replication process. I have asked client to kill that session and yes, the replication start showing decreasing pending commands and has made progress.

What this means is, in case you see the error The process is running and is waiting for response from the server you must need to check if there is any blocking process that is stopping your replication. In our case it was the issue along with Data Access property value for the Link Server that caused this issue.

Let me know if you have faced similar issue and how did you fixed it.

Leave a comment