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 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply