This will be a running post..
I will keep updating the posts as I create the query..
so let's begin
Question-> We need to find the jobs which are missing the run in the schedule.
Now what do we need to get this into action.
a. Job is currently running flag
b. Job Start Time
c. Next Schedule Date/Time
d. Job End Time
e. If Job End Time> Next Schedule Date/Time || If Schedule Date/Time is passed and Job Start Date Time is not equal to Schedule Date/Time
a. Job is curently running flag
This can be extracted from sysjobactivity and sysjobs
SELECT sj.Name,
CASE
WHEN sja.start_execution_date IS NULL THEN 'Not running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus',sja.job_id,sja.start_execution_date,sja.stop_execution_date
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);b. Job Start Time can also be pulled from SubQuery1
c. Next Schedule Date/Time
This we can pull from sysjobschedules
SubQuery2
SELECT j.job_id,convert(NVARCHAR(24),(msdb.dbo.agent_datetime(s.next_run_date,s.next_run_time)),121) NextSchedule
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobschedules s
ON s.job_id = j.job_id
d. Job End time from
SubQuery1
to be continued..
