Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List job name which didn't run today but supposed to run today. Expand / Collapse
Author
Message
Posted Saturday, November 3, 2012 5:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:36 PM
Points: 27, Visits: 287
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 '
Post #1380698
Posted Monday, November 5, 2012 5:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 3,907, Visits: 5,083
will this help:
;with LatestJobs AS
(SELECT Job_id, step_id, max(convert(varchar(8),run_date)+RIGHT('000000'+CONVERT(varchar(6), run_time),6)) As LastRun
from msdb.dbo.sysjobhistory
group by Job_id, step_id)
select j.job_id, j.name, next_run_date, next_run_time
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobhistory h on h.job_id = j.job_id
join LatestJobs lj on lj.job_id = j.job_id and lj.step_id = h.step_id and lj.LastRun = convert(varchar(8),run_date)+RIGHT('000000'+CONVERT(varchar(6), run_time),6)
join msdb.dbo.sysjobschedules s on s.job_id = j.job_id
where h.run_date < s.next_run_date and h.run_time < s.next_run_time
and convert(date,convert(varchar(8),ISNULL(NULLIF(s.next_run_date,0),19000101)),112) < convert(date, getdate())

please do not hesitate to work and / or refine this further if it does not meet all your requirements


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1381011
Posted Wednesday, October 30, 2013 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:42 AM
Points: 9, Visits: 153
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())
Post #1509891
Posted Wednesday, October 30, 2013 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:42 AM
Points: 9, Visits: 153
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
Post #1509944
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse