Auotomatic Sql server Job restarts once its Fails

Question

Auotomatic Sql server Job restarts once its Fails

vijay-473099

Mr or Mrs. 500

Points: 556
More actions
June 25, 2008 at 7:39 am

#122934

HI,
I have been doing some Job Monitoring Activity.Now wanted to Automise the same. So i have two task to be done
1> If some jobs Keep running for long time(assuming more then 10 Hr which is incorrect) I should have mechanism to stop it and restart the same - Programatically
2>If some jobs fails i should restart the same job after some Stipulated time(say after 1/2 hrs)- Programatically
Help would be appriciated!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MannySingh SSChampion Points: 12465 More actions · Answer 1

Is your Job executing a SP of if it contains a T-sQL, you will have to build the Logic inside the Code and check for Time.

Or use can experiment with sp_stop_job

[@job_name =] 'job_name'

| [@job_id =] job_id

| [@originating_server =] 'master_server'

| [@server_name =] 'target_server'

Maninder
www.dbanation.com

Animal Magic SSChampion Points: 13964 More actions · Answer 2

the way i get round this sort of thing is to look at the sysjobhistory (i think thats the table) and see if your job is in there after 10 hours (or whatever you want) of it starting (assuming its a fixed starting time). if the job is not in there for the latest execution it means its not finished and therefore you need to fire off an alert (i do it to an email to SMS provider so it wakes me up at 5 in the morning :angry:)

for the failed jobs you can do a very similar thing, look up what the status codes are and if the record is there (using above methods) with a failed status then you can restart the job sp_start_job in the same sql script that does the check.

hope this is enough to get you started.

vijay-473099 Mr or Mrs. 500 Points: 556 More actions · Answer 3

This May Stop the Jobs base on the logic i build.

But again how do i restart it again??

vijay-473099 Mr or Mrs. 500 Points: 556 More actions · Answer 4

Thanks Ya!!!

Will deveop the same login and get back if faces any issue!!

Again Appriciate for quike Help!!:)

Animal Magic SSChampion Points: 13964 More actions · Answer 5

vijay (6/25/2008)
This May Stop the Jobs base on the logic i build.
But again how do i restart it again??

well if its stopped (failed) then the status will say so, and you can just restart the job...

if job has failed

then sp_start_job @jobname

else return

edit:: just realised that last post wasnt for me 🙂

vijay-473099 Mr or Mrs. 500 Points: 556 More actions · Answer 6

vijay-473099

Mr or Mrs. 500

Points: 556

June 25, 2008 at 8:20 am

#834018

Never Mind ... 🙂

Many thanks for ur Help!!!:D