UNDERCOVER TOOLBOX: Sp_FailedJobs the quick way to check for Failed Agent Jobs.

I know I know, Why not just use the GUI right? well that’s ok but sometimes the GUI just takes too long for me…maybe I am a little impatient but sometimes I just need something to run that will give me the answers in just a couple of seconds with the touch of a button.

We decided to create a stored procedure for Failed Agent jobs because we got bored of using the View History option in the GUI for each job.

We receive Failed agent job notifications via email but these only advise you that a given job has failed, they do not include the reason for failure so if I want to see why that job failed then I need to go and check its history in SSMS via the View history option against the job in question, but what happens when you have multiple job failures? Maybe an AG has just failed over and now you have 100 or more job failures, do you really want to go and into each job history one by one?

Well fortunately there is another way, we can execute SP_FailedJobs.

So here is what it does:

1. Check for failed agent jobs within the dates you specify (provided the agent log has data for this period) @FromDate will default to the past 12 hours if  no value is passed in, @ToDate will default to GetDate() if no value is passed in.
2. Check that any failed jobs that have occurred within the date range have not subsequently succeeded, if the job has since succeeded since its last failure then this job will not be included in the results.
3. Check that the job is not currently running, if the job is still running at the time of executing the procedure then this job will be excluded from the results.
4. If a failed agent job exists that passes through the above checks then the Last Failure message will be obtained for the job and shown in the results along with the Job name, Failed Step and Failed Datetime.

 

Here is an example output from the Procedure:

And here is the Code:

USE [master]
GO

/************************************************
Author: Adrian Buckman
Date: 16/06/2017
SQLUnderCover.com
************************************************/

CREATE PROCEDURE sp_FailedJobs
(
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL
)
AS
BEGIN

IF @FromDate IS NULL BEGIN SET @FromDate = DATEADD(Minute,-720,GETDATE()) END
IF @ToDate IS NULL BEGIN SET @ToDate = GETDATE() END

SELECT
Jobs.name,
JobHistory.step_id,
JobHistory.FailedRunDate,
CAST(JobHistory.LastError AS VARCHAR(250)) AS LastError
FROM msdb.dbo.sysjobs Jobs
CROSS APPLY (Select TOP 1 JobHistory.step_id,JobHistory.run_date,
CASE JobHistory.run_date WHEN 0 THEN NULL ELSE
convert(datetime,
stuff(stuff(cast(JobHistory.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + JobHistory.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) END AS [FailedRunDate] ,[Message] AS LastError
FROM msdb.dbo.sysjobhistory JobHistory
WHERE
Run_status = 0
and Jobs.job_id = JobHistory.job_id
ORDER BY
[FailedRunDate] DESC,step_id DESC) JobHistory

WHERE Jobs.enabled = 1
AND JobHistory.FailedRunDate >= @FromDate AND JobHistory.FailedRunDate <= @ToDate
AND NOT EXISTS (SELECT [LastSuccessfulrunDate]
FROM(
SELECT CASE JobHistory.run_date WHEN 0 THEN NULL ELSE
convert(datetime,
stuff(stuff(cast(JobHistory.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + JobHistory.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) END AS [LastSuccessfulrunDate]
FROM msdb.dbo.sysjobhistory JobHistory
WHERE
Run_status = 1
AND Jobs.job_id = JobHistory.job_id
) JobHistory2
WHERE JobHistory2.[LastSuccessfulrunDate] > JobHistory.[FailedRunDate])
AND NOT EXISTS (SELECT Session_id
From msdb.dbo.sysjobactivity JobActivity
where Jobs.job_id = JobActivity.job_id
AND stop_execution_date is null
AND SESSION_id = (Select MAX(Session_ID) From msdb.dbo.sysjobactivity JobActivity
where Jobs.job_id = JobActivity.job_id)
)
AND Jobs.Name != 'syspolicy_purge_history'

ORDER BY name

END

 

Thanks for reading.