February 19, 2003 at 7:28 am
We use log shipping between several of our departmental servers to protect changes in real time data being captured, keeping a warm spare sitting literally beside the main server. We did this in SQL7, we continued it in SQL2000 using our same routines (we did not install Enterprise, these are tiny departmental servers, one I think we're evening using Data Engine for).
One of these (running Standard not DE) is sick. It copies the logs every 2 minutes, and it runs fine for days, then it starts getting behind. At least that's the sympton noted, but on looking more carefully part of what happens is that SQL Agent's CPU usage goes to 100% of one CPU (of 2). The disks are busy as well, it appears the restore is continuing, though more slowly due (I suspect) to CPU utilization.
SQLAgent runs the log shipping but shouldn't itself be doing the restore. And Task Manager shows SQL Agent doing all CPU -- no IO at all, when this happens.
Kill SQL Agent, start it up again, runs fine for a while (anywhere from minutes to hours) and then it goes into a CPU loop again.
Anyone seen anything like this?
PS. At SP2 on this system with the slammer patch, going to SP3 soon, but we have other SP2 systems that are fine. This one was fine, and for no identifiable reason this problem started.
February 19, 2003 at 9:51 am
I haven't seen this problem, nor I used it in 2K.
Try to set log shipping up from scratch on the problem server, shouldn't take more that 10-20 minutes.
When you say killed SQL Agent and then restarted how long does it take before the CPU is choked?
Before turning log shipping back on, run perfmon then try some aggresive queries, and perform a full backup of the database. If the CPU is pegged then you know it's not Log shipping.
When you run perfmon? Look at the CPU interrupts/sec compare to a similar built box, if you're experiencing a high level of interrupts it might be a sign of a high device requests, I've seen this happen with bad controllers. Disk IO shows normal, but the controller is bottled in passing the data up, this would cause device interrupts spiking the CPU.
John Zacharkan
Edited by - zach_john on 02/19/2003 10:22:32 AM
John Zacharkan
February 19, 2003 at 3:16 pm
It sometimes saturates in minutes, sometimes several days. Once is saturates i requires a restart of SQL agent.
Re your other questions, I think you misunderstand. It is SQL Agent not SQL Server that is saturating the CPU, it's not related to queries (SQL Agent doesn't execute the queries, SQL Server does). And it wa doing no IO, just consuming CPU (now the system overall as doing a lot of IO, but SQL Agent was not).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply