November 3, 2012 at 5:25 am
Hi,
I have one query.i face one issue almost every day.
We have lot of scheduled jobs configured ,some of jobs can't start on time as server may down at that time.
Can i get the query which will show 'that job didn't run today '
November 5, 2012 at 5:21 am
This was removed by the editor as SPAM
October 30, 2013 at 11:31 am
By the way, great post. I took the query provided and furthered refined it for jobs that have no history; you may have created a job yesterday that was supposed to run today. Also keep in mind that the query will return all columns and rows, so it's up to you to figure out which columns you want to see.
Select *
From msdb.dbo.sysjobs j with(nolock)
Inner Join msdb.dbo.sysjobschedules s with(nolock) on s.job_id = j.job_id
Outer Apply(
Select *,
RankID = Dense_Rank() Over(Partition By job_id Order By convert(varchar,run_date)+ ' ' + RIGHT('000000'+CONVERT(varchar, run_time),6) Desc)
From msdb.dbo.sysjobhistory with(nolock)
where
job_id = j.job_id) h
where
(h.RankID = 1 Or h.RankID Is Null) And
(h.run_date < s.next_run_date or h.run_date is null) and
(h.run_time < s.next_run_time or h.run_time is null) and
convert(date,convert(varchar(8),ISNULL(NULLIF(s.next_run_date,0),19000101)),112) < convert(date, getdate())
October 30, 2013 at 2:36 pm
Okay, so I forgot to include the enabled flag for the schedule. Here goes version 2:
Select *
From msdb.dbo.sysjobs j with(nolock)
Inner Join msdb.dbo.sysjobschedules js with(nolock) on js.job_id = j.job_id
Inner Join msdb.dbo.sysschedules s with(nolock) on s.schedule_id = js.schedule_id
Outer Apply(
Select *,
RankID = Dense_Rank() Over(Partition By job_id Order By convert(varchar,run_date)+ ' ' + RIGHT('000000'+CONVERT(varchar, run_time),6) Desc)
From msdb.dbo.sysjobhistory with(nolock)
where
job_id = j.job_id) h
where
(h.RankID = 1 Or h.RankID Is Null) And
(h.run_date < js.next_run_date or h.run_date is null) and
(h.run_time < js.next_run_time or h.run_time is null) and
convert(date,convert(varchar(8),ISNULL(NULLIF(js.next_run_date,0),19000101)),112) < convert(date, getdate()) and
j.enabled = 1 and
s.enabled = 1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply