June 16, 2009 at 10:44 am
I have a server with probably 50 or 60 jobs scheduled by the SQL Agent. Most of these jobs have steps that require retries for the jobs to succeed. No problems, the steps retry until the conditions needed for them to succeed are true, and the jobs eventually finish and report success... until...
A few new jobs were added, set up in exactly the same way as previous jobs, doing very similar things, etc. But when any step in these new jobs has to retry (which at least one of them inevitably does) then the entire job reports failure. This makes the "notification on failure" feature totally useless, as the jobs reports failure in all cases...
I can't find a setting anywhere in the job setup that addresses this, and I've tried "Script Job as --> Create To" to view the SQL behind the job and compared it to another job that is working fine... no luck.
Any help would be greatly appreciated!
Thanks
June 16, 2009 at 12:34 pm
It shouldn't have a problem with it unless one of the steps has "On failure quit job and report failure" as one of the possibilities. Are you sure they don't accidentally have one step like that?
If they don't, and you can't figure it out, you'll need to set up your own error handling step and turn off the automatic alerts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 1:06 pm
Yes, the On failure action is "Quit the job reporting failure." But when you have retry attempts specified, it iterates through all of the retries before failing the job. This is how all of our other jobs are set up, and when they retry one of their steps and then succeed, they report the job as having succeeded. Here's what we see for the jobs that are working properly:
Message
The job succeeded. The Job was invoked by Schedule 41 (schedule name). The last step to run was step 12 (last step in job).
And here's what we see for the jobs that are not working properly:
Message
The job failed. The Job was invoked by Schedule 155 (M - Sat). The last step to run was step 7 (last step in job).
It still gets all of the way through the job. It just reports failure. In the job history window, there's the red circle with white x through it instead of the yellow triangle with black exclamation point that we see whenever our other jobs succeed with a retry...
I think the next step will be to delete the jobs that are misbehaving and re-create them from scratch. Could just be a glitch...
June 16, 2009 at 2:07 pm
Since I can't look at the jobs, I don't have any suggestions beyond, "call Microsoft".
Not sure why there would be a difference.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 2:22 pm
one of my co-workers pointed out a difference, and this is probably it... she believes these jobs were created via in-line SQL rather than through the "New Job" GUI. so probably there's some setting or something in the SQL code that gets generated for creating jobs that caused this to happen. no other logical explanation. i'm going to drop one of the jobs and re-create it with the GUI...
thanks for your help.
June 17, 2009 at 7:00 am
That makes some sense. Let me know how it goes, please. I'm very curious.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply