SQL Job Notifications 1

[Alexisss]

Is there a way to have a notification set up to notify someone if a job does not complete within X minutes?

Thanks!

 

[JayKusch]

I have played w/ a similar instance of your question and YES ... you can set this type of alert up. What you need to key on are the fields in the MSDB..SysJobsHistory table, the creation of an initial step in your current job(s) and the addition of a status table for the job(s).

First item to add would be an initial step in your job(s) that writes the name of the job and the timestamp of when it was executed.

Then we need a second job that runs, say every minute or so, and reads from the status table all the jobs that are currently in there.

It will compare by job name, the start time with the corresponding record in the SysJobsHistory. If it exceeds your predefined number of minutes it needs to run, then create and send out the alert.

If you find that the job has completed under the mark set, remove it from the status table so that it is not picked up again.

Of course this is a bare-bones sketch of what needs to happen. You will need to evaluate the fields in the SysJobsHistory table in order to do you testing. I am sure there will need to be some CONVERTing of datetime fields to make it work as well as keeping track of what the job status but this should get you going in the right direction.



Thanks

J. Kusch

 

[Alexisss]

Thank you so much!