June 2, 2021 at 7:56 am
Hi There,
I need some script to take a report of failed agent job, only failed if it is success in next run ignore it.
I can able to get failed job, but if it success in next run it is generating that as well.
June 2, 2021 at 10:21 am
Use something like a NOT EXISTS clause to filter out anything that has had a subsequent success
Pseudo code
SELECT
job_name
FROM
msdb.dbo.sysjobhistory
WHERE
date >= convert(date,getdate())
AND
date < convert(date,getdate()+1)
AND
status = 'failed'
AND NOT EXISTS
(SELECT
job_name
FROM
msdb.dbo.sysjobhistory
WHERE
date >= convert(date,getdate())
AND
date < convert(date,getdate()+1)
AND
status = 'success'
)
June 3, 2021 at 11:30 am
Thanks, that does not working.
June 3, 2021 at 3:07 pm
for the query below, the h.[run_status] is what determines what was successful/failed/inprogress.
add the filter to limit it to failed only, and change the date range.
--desc: gets job history, and when the GUI times out
DECLARE @Job_name varchar(128) = ''; --optional search for a specific job
SELECT TOP 100
CASE h.[run_status]
WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'InProgress'
ELSE 'Retry'
END AS JobStatus,
j.name as 'JobName',
s.step_id as 'Step',
s.step_name as 'StepName',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as 'RunDurationMinutes',
h.[message]
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h
ON s.job_id = h.job_id
AND s.step_id = h.step_id
AND h.step_id <> 0
where 1=1
AND j.enabled = 1 --Only Enabled Jobs
and (j.name = @Job_name OR @Job_name = '' )
and msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN '2021-05-01' and '2021-06-01' --Uncomment for date range queries
order by JobName, RunDateTime desc
Lowell
June 4, 2021 at 5:30 am
Thank you Lowell. I got similar kind of code from some website. But, I do not know, how to remove next run success job from the result.
Ex: job_A runs every 5 minutes - It failed at 11 AM and success at 11:05, which means it should not be appear in the result.
Ex: job_B runs every 5 minutes - It failed at 11 AM, success at 11:05, failed 11:10 which means it should be appear in the result.
When I run the query to collect the report, if the job run is success in the last run, even if it failed earlier runs can be ignored. If the last run is failed add that into a report. if it has multiple entries, just add only one entries (Tried with row_number).
I tried some CTE, not working as expected.
June 4, 2021 at 2:22 pm
ok try this:
this returns only jobs whos last status was failed.
ignore the extra variables in there, it is an artifact of a process I run that gets the fully qualified server name(is MyRealServer.mydomain.com,1433 and also what layer it might be (ie Dev, UAT, Prod, whatever)
-- Failed Jobs executed since the Last run hours, where the last status is failed.
--This skips jobs that failed once,and subsequently executed successfully on the following run(s).
DECLARE @Layer VARCHAR(30); SET @Layer = '[CMSServerLayer]'
DECLARE @CMSFQServerName VARCHAR(128); SET @CMSFQServerName = '[CMSFQServerName]'
DECLARE @dt CHAR(8);
SET @dt= CASE
WHEN DATENAME(dw,GETDATE()) = 'Monday'
THEN CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-3), GETDATE())), 112)
ELSE CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
END
SELECT
@CMSFQServerName AS CMSFQServerName,
@Layer AS ServerLayer,
CONVERT(varchar(128),@@SERVERNAME) As Servername,
T1.step_name AS [Step Name],
SUBSTRING(T2.name,1,140) AS [SQL Job Name],
--msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
COUNT(*) AS TotalFailures,
CAST(MIN(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) AS CHAR(11)) AS [MinFailure Date],
CAST(MAX(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) AS CHAR(11)) AS [MaxFailure Date],
MAX(msdb.dbo.agent_datetime(T1.run_date, T1.run_time)) AS 'RunDateTime',
MIN(T1.run_duration) StepDuration,
CASE MIN(T1.run_status)
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
MAX(T1.message) AS [Error Message]
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE
T1.run_status NOT IN (1,2,4)
AND T1.step_id != 0
AND run_date >= @dt
GROUP BY
T1.step_name,
T2.name
Lowell
June 5, 2021 at 3:05 am
Thank you Lowell. It is working good.
I just have few doubts.
Is there any difference to use this, both are returning same date and format.
"SET @dt=CONVERT(CHAR(8),DATEADD(HH,-24,GETDATE()),112) " . Since yours showing some extra rows than this.
DECLARE @dt CHAR(8);
SET @dt= CASE
WHEN DATENAME(dw,GETDATE()) = 'Monday'
THEN CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-3), GETDATE())), 112)
ELSE CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
END
SET @dt=CONVERT(CHAR(8),DATEADD(HH,-24,GETDATE()),112)
June 5, 2021 at 12:00 pm
it might not be obvious, but the query is assuming a monday thru friday work week.
so if today is monday, i want errors from Sat + Sun + so far today, otherwise, just the errors in the last 24 hours.
it's basically the same code i use as part of a daily report each day, to give me an idea of issues.
if you change it to just last 24 hours like you are suggesting, i would assume you check the report each day.
Lowell
June 6, 2021 at 5:26 am
Thank you for your help and response. Happy learning.
Have great weekend.
June 7, 2021 at 8:54 am
Hi Lowell,
I run into another problem, I when run this code to get the report almost 10+servers, I could get lot of failed jobs ex:100+ jobs.
When I go and manually check the next run it automatically got successful. Is there any way to exclude those, if the multiple times failed job is successful in the last run those can be exclude, so that it is easy work for me to go and check job manually only the failed job that never successful till my report run.
June 7, 2021 at 6:22 pm
SELECT j.name AS job_name, jh.*
FROM (
SELECT jh.job_id, MAX(jh.instance_id) AS instance_id
FROM msdb.dbo.sysjobhistory jh
WHERE jh.run_status = 0 AND
/* limit to last 7 days only -- adjust this as you need to */
jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -7, GETDATE()), 112)
GROUP BY jh.job_id
) AS failed_jobs
INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id = failed_jobs.instance_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
WHERE
NOT EXISTS(
SELECT 1
FROM msdb.dbo.sysjobhistory jh
WHERE
jh.instance_id > failed_jobs.instance_id AND
jh.job_id = failed_jobs.job_id AND
jh.step_id <= jh.step_id
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 9, 2021 at 2:40 am
Hi ScottPletcher,
It is not working.
This is working same as Lowell script, but the whole script returns zero rows.
I have checked some job is failed in the last run, that is not reporting from the query you have shared. I think some issue with if exists.
SELECT jh.job_id, MAX(jh.instance_id) AS instance_id
FROM msdb.dbo.sysjobhistory jh
WHERE jh.run_status = 0 AND
jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -1, GETDATE()), 112)
GROUP BY jh.job_id
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply