The process is running and is waiting for a response from the server - transactional replication

Question

I could not yet figure it out why one of my transaction replication subscription is constantly coming out with the following annoying message:

The process is running and is waiting for a response from the server. Query Timeout Expired.

I have tried to increase the timeout on the Log Reader Agent Profile, but it is read-only. I have also tried to create another profile and associate it with the Log Reader Agent, but it is not applied.

Questions:

1) How can I change the Log Reader Agent Profile via T-SQL? That would give me the proper error message.

2) How can I find out what is causing the timeout, where is it stuck?

From my previous experience, this error message disappears with time, but in this case it is not going away, and I feel that I need to change some setting, but I would like first to see clear where it is stuck, in order to assess best what exactly to change and why.

Answer 1

The Query Timeout message you are seeing is your Distribution Agent timing out, not your Log Reader Agent. You need to increase your Distribution Agent profile Query Timeout parameter value to alleviate the error. Please see the section To view and edit the parameters associated with a profile in Work with Replication Agent Profiles to edit the QueryTimeout parameter value for your Distribution Agent.

Note that if you run your Distribution Agent continuously that you will need to restart your Distribution Agent for the profile change to take affect.