Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Monitoring Failed Job Steps

By Leo Peysakhovich,

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:

 Job Step Name On Success On Failure
Check all databases Go to next Step Go to next Step
Cycle Error Log Go to next Step Go to next Step
Report Folder Sizes Go to next Step Go to next Step
Database Size Report Go to next Step Go to next Step
Check failed steps Quit the job reporting success Quit the job reporting failure

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

 Job Step Name On Success On Failure
Extract Data Go to next Step Go to step : 5
Prepare File Data Go to next Step Go to step : 5
Create File Go to next Step Go to step : 5
Send File Go to next Step Go to step : 5
Check failed steps Quit the job reporting success Quit the job reporting failure

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.

Total article views: 9022 | Views in the last 30 days: 6
 
Related Articles
FORUM

Stored Procedure with varchar(max) as parameter

Stored Procedure with varchar(max) as parameter

FORUM

Check box in report

Check box in report

SCRIPT

Random varchars

This procedure generates a list of random varchars (8 charactes length, only letters - lower or uppe...

FORUM

help in my procedure

Procedure

FORUM

Checking Admin User - Stored procedure

stored procedure

Tags
administration    
monitoring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones