March 24, 2023 at 10:35 pm
I have been trying to find SQL that shows what SQL Agent jobs failed as our manager wants a report generated showing what jobs fail. The below SQL I have found but it shows which steps fail but that is not what I want. Reason being is that we have an AG environment and the first step of the job checks to see if it is the primary and if it isn't that failed step we mark as success and end the job. However that step actual fails. That is causing this SQL to show step failures not if the actual job fails. Does anyone have anything that could help with this?
WITH FailedJobs AS( SELECT SERVERPROPERTY('Servername') AS ServerName , sysjobhist.job_id, job.name AS JobName, job.description AS JobDescription, sysjobhist.step_name AS JobStep ,CASE sysjobhist.run_date WHEN 0 THEN CONVERT(DATETIME, '1900/1/1') ELSE CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':')) END AS LastRunDate ,sysjobhist.run_status ,CASE sysjobhist.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In Progress' ELSE 'Unknown' END AS run_status_desc ,sysjobhist.run_duration AS RunTimeInSeconds ,sysjobhist.message ,SUSER_SNAME(job.owner_sid) AS owner ,o.name AS notification_owner ,CASE schedule.freq_type WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQL Server Agent starts' WHEN 128 THEN 'Start whenever the CPU(s) become idle' ELSE '' END AS FequencyType ,CASE schedule.freq_type WHEN 1 THEN 'O' WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, schedule.freq_interval) + ' day(s)' WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' weeks(s) on ' + LEFT(CASE WHEN schedule.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END, LEN(CASE WHEN schedule.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN schedule.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END) - 1) WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, schedule.freq_interval) + ' of every ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' month(s)' WHEN 32 THEN 'The ' + CASE schedule.freq_relative_interval WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' END + CASE schedule.freq_interval WHEN 1 THEN ' Sunday' WHEN 2 THEN ' Monday' WHEN 3 THEN ' Tuesday' WHEN 4 THEN ' Wednesday' WHEN 5 THEN ' Thursday' WHEN 6 THEN ' Friday' WHEN 7 THEN ' Saturday' WHEN 8 THEN ' Day' WHEN 9 THEN ' Weekday' WHEN 10 THEN ' Weekend Day' END + ' of every ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' month(s)' ELSE '' END AS Occurence ,CASE schedule.freq_subday_type WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') WHEN 2 THEN 'Occurs every ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Seconds(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':') WHEN 4 THEN 'Occurs every ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':') WHEN 8 THEN 'Occurs every ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':') ELSE '' END AS Frequency FROM msdb.dbo.sysjobhistory AS sysjobhist JOIN msdb.dbo.sysjobs job ON JOB.job_id = sysjobhist.job_id LEFT JOIN [dbo].[sysoperators] o ON job.[notify_email_operator_id] =o.[id] LEFT JOIN msdb.dbo.sysjobschedules AS jobschedule ON job.job_id = jobschedule.job_id LEFT JOIN msdb.dbo.sysschedules AS schedule ON jobschedule.schedule_id = schedule.schedule_id WHERE YEAR(CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))) >= 2022 AND sysjobhist.step_id <> 0 ) SELECT fb.*, ROW_NUMBER() OVER ( PARTITION BY job_id,fb.JobName ORDER BY fb.LastRunDate ) AS RowOrder FROM FailedJobs fb WHERE Fb.run_status_desc = 'Failed'
March 25, 2023 at 9:22 am
You can stop treating a normal condition as a failure that can be ignored by adding the check for primary into each step that does work that should only occur on the primary. Instead of failing, log that <backup, ETL, etc.> will be skipped because the local target database is not the primary replica, then exit the step with success.
Build your systems so all failures are real failures that should be investigated and resolved.
Eddie Wuerch
MCM: SQL
March 25, 2023 at 10:24 am
code above formatted in case anyone interested in seeing what it it
with failedjobs
as
(select serverproperty('Servername') as ServerName
, sysjobhist.job_id
, job.Name as JobName
, job.description as JobDescription
, sysjobhist.step_name as JobStep
, case sysjobhist.run_date
when 0
then convert(datetime, '1900/1/1')
else convert(datetime, convert(char(8), sysjobhist.run_date, 112) + ' ' + stuff(stuff(right('000000' + convert(varchar(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))
end as LastRunDate
, sysjobhist.run_status
, case sysjobhist.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In Progress'
else 'Unknown'
end as run_status_desc
, sysjobhist.run_duration as RunTimeInSeconds
, sysjobhist.message
, suser_sname(job.owner_sid) as owner
, o.Name as notification_owner
, case schedule.freq_type
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly relative'
when 64 then 'When SQL Server Agent starts'
when 128 then 'Start whenever the CPU(s) become idle'
else ''
end as FequencyType
, case schedule.freq_type
when 1 then 'O'
when 4 then 'Every ' + convert(varchar, schedule.freq_interval) + ' day(s)'
when 8 then 'Every ' + convert(varchar, schedule.freq_recurrence_factor) + ' weeks(s) on '
+ left(case when schedule.freq_interval & 1 = 1 then 'Sunday, ' else '' end
+ case when schedule.freq_interval & 2 = 2 then 'Monday, ' else '' end
+ case when schedule.freq_interval & 4 = 4 then 'Tuesday, ' else '' end
+ case when schedule.freq_interval & 8 = 8 then 'Wednesday, ' else '' end
+ case when schedule.freq_interval & 16 = 16 then 'Thursday, ' else '' end
+ case when schedule.freq_interval & 32 = 32 then 'Friday, ' else '' end
+ case when schedule.freq_interval & 64 = 64 then 'Saturday, ' else '' end
, len(case when schedule.freq_interval & 1 = 1 then 'Sunday, ' else '' end
+ case when schedule.freq_interval & 2 = 2 then 'Monday, ' else '' end
+ case when schedule.freq_interval & 4 = 4 then 'Tuesday, ' else '' end
+ case when schedule.freq_interval & 8 = 8 then 'Wednesday, ' else '' end
+ case when schedule.freq_interval & 16 = 16 then 'Thursday, ' else '' end
+ case when schedule.freq_interval & 32 = 32 then 'Friday, ' else '' end
+ case when schedule.freq_interval & 64 = 64 then 'Saturday, ' else '' end
) - 1)
when 16
then 'Day ' + convert(varchar, schedule.freq_interval) + ' of every ' + convert(varchar, schedule.freq_recurrence_factor) + ' month(s)'
when 32
then 'The ' +
case schedule.freq_relative_interval
when 1 then 'First'
when 2 then 'Second'
when 4 then 'Third'
when 8 then 'Fourth'
when 16 then 'Last'
end +
case schedule.freq_interval
when 1 then ' Sunday'
when 2 then ' Monday'
when 3 then ' Tuesday'
when 4 then ' Wednesday'
when 5 then ' Thursday'
when 6 then ' Friday'
when 7 then ' Saturday'
when 8 then ' Day'
when 9 then ' Weekday'
when 10 then ' Weekend Day'
end + ' of every ' + convert(varchar, schedule.freq_recurrence_factor) + ' month(s)'
else ''
end as Occurence
, case schedule.freq_subday_type
when 1
then 'Occurs once at ' + stuff(stuff(right('000000' + convert(varchar(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':')
when 2
then 'Occurs every ' + convert(varchar, schedule.freq_subday_interval) + ' Seconds(s) between ' + stuff(stuff(right('000000' + convert(varchar(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + stuff(stuff(right('000000' + convert(varchar(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
when 4
then 'Occurs every ' + convert(varchar, schedule.freq_subday_interval) + ' Minute(s) between ' + stuff(stuff(right('000000' + convert(varchar(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + stuff(stuff(right('000000' + convert(varchar(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
when 8
then 'Occurs every ' + convert(varchar, schedule.freq_subday_interval) + ' Hour(s) between ' + stuff(stuff(right('000000' + convert(varchar(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + stuff(stuff(right('000000' + convert(varchar(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
else ''
end as Frequency
from msdb.dbo.sysjobhistory as sysjobhist
join msdb.dbo.sysjobs job
on job.job_id = sysjobhist.job_id
left join msdb.dbo.sysoperators o
on job.notify_email_operator_id = o.[id]
left join msdb.dbo.sysjobschedules as jobschedule
on job.job_id = jobschedule.job_id
left join msdb.dbo.sysschedules as schedule
on jobschedule.schedule_id = schedule.schedule_id
where year(convert(datetime, convert(char(8), sysjobhist.run_date, 112) + ' ' + stuff(stuff(right('000000' + convert(varchar(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))) >= 2022
and sysjobhist.step_id <> 0
)
select fb.*
, row_number() over (partition by job_id, fb.jobname order by fb.lastrundate) as roworder
from failedjobs fb
where fb.run_status_desc = 'Failed'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply