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.
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.