I want to query the system tables to get a list of jobs that failed. I only want to return the most recent jobs that failed. If a job failed yesterday, but then the issue was fixed and job executed again and succeeded, this job should not appear in the resultset as the latest status was "success". If a job failed, I also want to get the last time the job was successful. I am having a little bit of an issue with this and hoping someone could point out the issue in my query:
SELECT DISTINCT
J.NAME AS "Job_Name",
J.DESCRIPTION AS "Job_Description",
H.Step_Name AS "Step_Name",
H.MESSAGE AS "Error_Message",
MSDB.dbo.Agent_Datetime(h.run_date, h.run_time) AS Last_Status_Date,
(SELECT MAX(MSDB.dbo.Agent_Datetime(run_date, run_time)) FROM MSDB.dbo.SysJobHistory WHERE run_status = 1 and step_id = 0 AND job_id = j.job_id) AS Last_Successful_Date
--,
--h.run_date,
--h.run_time
--MAX(h.run_time)
FROM
MSDB.dbo.SysJobHistory H,
MSDB.dbo.SysJobs J
WHERE
J.job_id = H.job_id
AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id)
AND h.run_status = 0 -- Only Failed Jobs
AND h.step_name <> '(Job outcome)'
AND J.NAME NOT LIKE 'DBAMaint_%'
AND J.NAME <> 'Configure_Verify_Policies'
AND J.NAME <> 'SQL_Query_Trace'
ORDER BY
J.NAME
October 19, 2021 at 2:57 pm
This is the issue
AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id)AND h.run_status = 0 -- Only Failed Jobs
The run_status needs to go into the subquery
AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id AND hi.run_status = 0) -- Only Failed Jobs
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Does this get you anywhere? Haven't got any failed jobs to test it on at the moment.
WITH OrderedJobs
AS
(SELECT
j.job_id
, j.name
, jh.run_status
, jh.run_date
, jh.run_time
, rn = ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY jh.run_date DESC, jh.run_time DESC)
FROM dbo.sysjobs j
JOIN dbo.sysjobhistory jh
ON jh.job_id = j.job_id
WHERE jh.step_id = 0)
, LastSuccessful
AS
(SELECT
j.job_id
, jh.run_status
, jh.run_date
, jh.run_time
, rn = ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY jh.run_date DESC, jh.run_time DESC)
FROM dbo.sysjobs j
JOIN dbo.sysjobhistory jh
ON jh.job_id = j.job_id
WHERE jh.step_id = 0
AND jh.run_status = 1)
SELECT
oj.name
, oj.run_status
, oj.run_date
, oj.run_time
, oj.rn
, LastSuccessfulRunDate = ls.run_date
, LastSuccessfulRunTime = ls.run_time
, LastSuccessfulRunStatus = ls.run_status
FROM OrderedJobs oj
JOIN LastSuccessful ls
ON ls.job_id = oj.job_id
WHERE oj.rn = 1
AND oj.run_status <> 1
AND ls.rn = 1;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 19, 2021 at 4:37 pm
Michael:
Thank you for the suggestion, but unfortunately this brings back all jobs that failed regardless of the date, so it will show the last time a job failed, even if it was a few weeks ago, but the Last_Successful_Date will be greater.
October 19, 2021 at 5:00 pm
Michael:
Thank you for the suggestion, but unfortunately this brings back all jobs that failed regardless of the date, so it will show the last time a job failed, even if it was a few weeks ago, but the Last_Successful_Date will be greater.
Duh. Whoops. Phil's solution works fine!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 19, 2021 at 5:50 pm
Phil:
After playing with that query, I notice it gives me all the "Step_Names" as being "(Job Outcome)" and the Job_Descriptions are all of the job itself, but not the step that failed.
I would really like to get the Error Message and Step Name of the part of the job that failed to display. I believe I can do this by doing a sub_query in the final select statement, but wasn't sure if this is the best method or not and not quite understanding how to exactly get this.
October 20, 2021 at 7:05 am
Tks a lot, i'm having question like u!
Dang Tin Cho Thue Can Ho Chung Cu Mien Phi- Thuecanho123com
October 20, 2021 at 8:17 am
Phil:
After playing with that query, I notice it gives me all the "Step_Names" as being "(Job Outcome)" and the Job_Descriptions are all of the job itself, but not the step that failed.
I would really like to get the Error Message and Step Name of the part of the job that failed to display. I believe I can do this by doing a sub_query in the final select statement, but wasn't sure if this is the best method or not and not quite understanding how to exactly get this.
That is a bit more involved, especially given the ability to tweak the 'On failure action' for each step. As I do not have a load of failed jobs on which to test, I don't feel confident that I could code a properly working solution for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 20, 2021 at 11:10 am
Phil:
I understand and just very appreciative of all the help you always provide.
October 20, 2021 at 7:17 pm
What about this one?
SELECT
j.job_id AS FailedJobId,
j.name AS FailedJobName,
jh.instance_id AS FailedJobInstance,
jh.run_date AS FailedJobLastRunDate,
jh.run_time AS FailedJobLastRunTime,
js.step_id AS FailedStepNbr,
js.step_name AS FailedStepName,
jh.message AS FailedStepError
FROM
dbo.sysjobs j
INNER JOIN
dbo.sysjobhistory jh
ON jh.job_id = j.job_id
CROSS APPLY (
SELECT TOP(1)
instance_id,
step_id,
step_name
FROM
msdb.dbo.sysjobsteps
WHERE
job_id = j.job_id
AND step_id = jh.step_id
ORDER BY
instance_id DESC
) AS js
WHERE
jh.run_status = 0
AND NOT EXISTS (
SELECT *
FROM dbo.sysjobhistory
WHERE job_id = j.job_id
AND ((run_date > jh.run_date) OR (run_date = jh.run_date AND run_time > jh.run_time))
AND run_status = 1
)
AND jh.instance_id = js.instance_id
October 20, 2021 at 9:42 pm
... or maybe this one?
select
js.job_id,
j.name,
js.last_outcome_message,
jst.subsystem,
jh.step_id,
jh.step_name,
jh.message,
lgr.last_run_date
from [dbo].[sysjobservers] js
inner join [dbo].[sysjobs] j on j.job_id=js.job_id
cross apply (
select top(1) *
from [dbo].[sysjobactivity]
where job_id = j.job_id
order by [session_id] desc
) ja
cross apply (
select top(1) *
from [dbo].[sysjobhistory]
where job_id=j.job_id and step_id=ja.last_executed_step_id
order by instance_id desc
) jh
inner join [dbo].[sysjobsteps] jst
on jst.job_id=j.job_id and jst.step_id=ja.last_executed_step_id
outer apply (
select top(1) *
from [dbo].[sysjobservers]
where job_id=js.job_id
and server_id=js.server_id
and js.last_run_outcome = 1
order by last_run_date desc, last_run_time desc
) lgr
where js.last_run_outcome = 0
and js.server_id=0
I actually "think" this one may be better than the last one I submitted, but you'll need to test them, because I don't have many failed jobs for testing either.
The first one relied somewhat on the instance, but I found out that this instance isn't a jobrun instance, but something else. It's pretty confusing how these tables relate to each other.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply