One day I became tired of doing a manual procedure to check every morning to

see if nightly backup, data transfer, and other jobs have failed. I decided to

develop code that will automatically verify if jobs have failed and email the

results to me.

One way is to accomplish this is goal is to make sure the last step of any job

is 'Check Failed Steps'. This works as long as the last step of the job is the

only one to have an on failure action of 'Quit the job reporting failure'. For

example:

In some cases if a step fails you'll want to branch directly to the failed

step:

P_JOB_STEP_FAIL_1.prc contains the

stored procedure code to check failed steps for a job. This only checks the last

(current) instance of the job to run. To run it, the syntax is:

exec dbo.P_JOB_STEP_FAIL @job_name VARCHAR(50), @recipients varchar(100)

P_ANY_JOB_STEP_FAIL_1.rpc

notifies you of any failed job step within the last x hours. I typically a

separate job for this procedure and schedule it to run every 3-4 hours checking

8 hours back. Synax is as follows:

Exec dbo.P_ANY_JOB_STEP_FAIL @recipients varchar(100), @hours_back int = -12

Of course, I am using msdb system tables sysjobhistory and sysjobs. No

special permissions are required because they run as a part of a job.

Conclusion

It is may be not a big deal to check jobs daily and control them manually.

But this simple 2 way automation helps me, especially when I maintain multiple

servers and many jobs. And it gives me the pleasure of additional control. I

have been using these procedures for almost 2 years and didn’t have issues or

problems so far with proper error notification.