sp_help_jobhistory - Check failing Jobs

sp_help_jobhistory system stored procedure is used to view the history of jobs. For checking the history of all Jobs we can execute below script.


USE msdb;
GO
EXEC dbo.sp_help_jobhistory
GO

We can also execute this SP to check for only failing Jobs by providing @run_status parameter. Below SP call would provide the list of Jobs with steps details which failed to get execute. Specifying a value of 0 for the @run_status parameter returns history for all failed jobs.


USE msdb;
GO
EXEC dbo.sp_help_jobhistory
@run_status = 0;
GO

Specifying a value of 1 for the @run_status parameter returns history for all jobs that completed successfully.


Specifying a value of 2 for the @run_status parameter returns history for all jobs steps that have a retry status.


Specifying a value of 3 for the @run_status parameter returns history for all jobs that were canceled.


This SP can also be executed with @Job_id /@Job_name parameters too to check for particular Job's history. If we do not pass any value to these parameters this SP runs for all Jobs.


There are more parameters to this SP, which I don't think are that much handy then the above 3 described though I have provided the list of all parameters below.

Parameter_name	Type
@job_id	uniqueidentifier
@job_name	sysname
@step_id	int
@sql_message_id	int
@sql_severity	int
@start_run_date	int
@end_run_date	int
@start_run_time	int
@end_run_time	int
@minimum_run_duration	int
@run_status	int
@minimum_retries	int
@oldest_first	int
@server	sysname
@mode	varchar