SQL Jobs Running at Certain Time

What jobs are running at a given time

 

 


/*
What jobs are running during a given time.
*/
declare @time datetime

set @time = '2019-06-02 21:00'

SELECT * FROM
(
 SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds
 FROM
 (
  SELECT j.name AS 'JobName',
    msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
    ((jh.run_duration/1000000)*86400) 
    + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600) 
    + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60) 
    + (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
  FROM msdb.dbo.sysjobs j 
  INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id 
  WHERE jh.step_id=0 --The Summary Step
 ) AS H
) AS H2
WHERE 1=1
and @time BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd

 

Leave a Reply

Your email address will not be published. Required fields are marked *