June 10, 2008 at 10:33 am
Hi, I was having difficulty getting snapshots done. Some publications that only have a few small articles and that used to finish snapshotting in a few seconds just hang. I got 'The process is running and is waiting for a response from the server'. I got every other runnable processes disconnected, thinking it was because of the contention on the server, but did not work. Btw, our publisher and distributor are on the same server. Which I guess should not matter, because the snapshotting worked fine two days ago.
Thank you for any insight on this.
Kathleen
June 10, 2008 at 3:32 pm
The response that is wating is probably the one on the "replica". You should check for contention on both distribution and subscription database.
* Noel
June 10, 2008 at 5:03 pm
Thanks Noel.
I just tried to re-snapshot a publication and got the same message on snapshot agent 'waiting response from the server', then I checked the sysprocesses table and found 'sos_scheduler_yield' wait type by the agent. I guess its an indicator of contention on the worker threads on the sql server agents, because we altogether have 130 jobs running under the sql server agent. I figured that I should disable most of the processes being running the snapshots again. But I won't be able to test it until 5am tomorrow. I will post the results then.
Thanks for your help!
June 11, 2008 at 10:42 am
KATHLEEN Y ZHANG (6/10/2008)
Thanks Noel.I just tried to re-snapshot a publication and got the same message on snapshot agent 'waiting response from the server', then I checked the sysprocesses table and found 'sos_scheduler_yield' wait type by the agent. I guess its an indicator of contention on the worker threads on the sql server agents, because we altogether have 130 jobs running under the sql server agent. I figured that I should disable most of the processes being running the snapshots again. But I won't be able to test it until 5am tomorrow. I will post the results then.
Thanks for your help!
From your info: high percent of waits in SOS_SCHEDULER_YIELD is an indication that your server (in this case the primary) is "overloaded" at that time; CPU probably is really close to 100%.
* Noel
June 11, 2008 at 10:52 am
Hi, Noel, thanks for the insight.
I was able to get it done this morning. The CPU was actually ~20%. I temporarily disabled most jobs and log reader and distribution agent jobs that I did not need at the moment. It went through pretty fast. I guess what was happening before was the snapshot agents were waiting for a scheduler thread from sql server agent.
June 11, 2008 at 11:39 am
Glad you made it work.
Snapshot agents tend to be CPU intensive.
Probably the load from All other jobs was affecting the entire process.
Anyway. Thanks for posting back
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply