January 11, 2010 at 10:06 am
We had a SQL 2005 standard edition server down this morning. When it came back online, SQL Server and SQL Server Agent automatically restarted as expected. What came as a suprise, though, is that the first thing Agent did was run all jobs that had a missed scheduled run during the outage.
I am trying to find:
1. Where this behavior is documented.
2. Confirmation that this was new in 2005 (don't remember this in 2000).
3. A method for turning it off. I absolutely do not want SQL Agent doing this. I want to decide for myself if it is a good time to rerun the job or not.
Anyone have an insights/advice?
January 11, 2010 at 10:37 am
You don't have a schdule setup on those jobs that says "Run on Agent startup"?
Which would obviously run the jobs specified.
Gethyn Elliswww.gethynellis.com
January 11, 2010 at 11:16 am
Unless you have them set to start on startup, they should not run.
January 11, 2010 at 12:08 pm
Steve -
That is exactly what I thought, which is why I was shocked to see some of the jobs had run.
I have just spent some more time going through the event logs and I now believe it has to do with how the server was brought back online.
The first report that I got from the server engineer that tried to bring it back up this morning was that it "might be dead". Doh! Apparently it was just sitting there beeping and when he did a hard boot it did nothing and just returned to beeping. He ended up getting it back by swapping the drive to a different server.
The logs of the recovered server since recovery make it appear as though SQL Server was actually running at the very moment the server was brought back online rather than having to start it at that time. SQL agent kicked off the jobs at that point. Then, about two minutes later, the message "SQL Server is terminating because of a system shutdown" appears. I do not see any service starting events before the jobs kicked off for either agent or sql server.
I'm trying to piece it together a little better, but it looks now like the jobs were not started at SQL Agent startup (like it had originally appeared) but at server recovery prior to SQL being restarted.
Wow. I am glad none of those jobs did major damage by running off schedule like they did.
Funny how when you can't find documentation of a behavior you are seeing, there is usually something else going on there. And I would have bet any amount of money that SQL Agent did not try to run missed jobs upon startup, but there they were... about a half dozen jobs that kicked off at the same time, when the server was recovered, not on their normal schedule, and without run on startup schedules.
January 11, 2010 at 1:07 pm
Very strange. Is it possible the time was off on the new machine, or that the processes kicked off as part of rollback/rollforward?
Are you sure they kicked off because of agent?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply