I recently set up a server, wrote some simple procedures to do things like backup, copied these into a job to run once per day starting at 05.00am - 08.00pm

begin
 call BackupDatabase;
... etc.
end

& wet away a happy bunny.

I didn't realise that the procedure needed a "USE [databsename]" statement to be added in order to work.

Later I noticed the server was max'd out at 100% CPU usage.

Opening it there didn't seem to be anything amiss, until I tried:

SELECT * FROM LogEvents

... this showed that my JOB was running roughly 100 times per second ...

Basically because the Procedure failed, the job does not register as having run, so the server tries to run it again, at which point it fails again ... ad infinitum.

--

I guess its a newbie error, but I thought I'd post this as a warning ... it is easy to make & a bit complicated to get to the bottom of. I hope this is useful for someone.



1. Should have tested the job properly from the perspective of the SESSION not just within the DB.
2. Shouldn't have had the job run for the whole day from 5 through to 8 ... If I had only given it a 5 minute window the server would only have max'd out for that long.
3. I ought to write some maintenance code to regularly check for errors being logged in the LogEvents table ... to bring this sort of stuff to my attention.

Adam,

<< .. this showed that my JOB was running roughly 100 times per second ...
>>

Hmmm, it shouldn't be trying to run the job that often - it should be
re-attempting the execution every *minute*.

Can you replicate this happening ?  If so, please send me what you've got
and I'll find out what is going on.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com