Thursday, November 3, 2022

Interested in Jobs missing Schedules

Hi Peeps, 
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..

No comments:

Post a Comment