Sql-server – How to find Jobs that ran between two times

jobssql server

Is there a query to check what a Jobs that ran between to given times. I can check the Jobs that are scheduled between two times but I dont want that.

Example I want to know what the Jobs that ran between 16:00:00 and 17:00:00

There can be cases where job scheduled at 15:00:00 might running for more than 1 hour I want those as well. I did search in google all I got is jobs those are scheduled between two times

Best Answer

Well you could run the following query to retrieve the jobs run between two times or you could change the WHERE expression to catch jobs that ran for a certain duration.

SELECT sj.name,
       sjh.step_name,
       sjh.step_id,
       --sjh.run_status,
       STUFF(STUFF(CAST(sjh.run_date as nvarchar(10)),5,0,'.'),8,0,'.') as HistRunDate,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as HistRunTime,
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as HistRunDuration
       --,sjh.run_status AS JobStatus
FROM   msdb.dbo.sysjobs           AS sj
       join msdb.dbo.sysjobhistory AS sjh --- was sysjobschedule sjc
            on sjh.job_id = sj.job_id
WHERE  1=1
--AND sj.enabled = 1
------------------------------------------
-- for a certain time frame
------------------------------------------
AND ((sjh.run_date = 20170617 and sjh.run_time > 200000) 
or (sjh.run_date = 20170722 and sjh.run_time < 100000))

------------------------------------------
-- between certain dates and with a long duration
------------------------------------------
--AND (sjh.run_date > 20160501) AND (sjh.run_date < 20160503)
--and sjh.run_duration > 3000

------------------------------------------
-- Job Outcome not required
------------------------------------------
--and sjh.step_name != '(Job outcome)'
--and sjh.step_id = 0

------------------------------------------
-- Find failed jobs
------------------------------------------
--and sjh.run_status != 1


ORDER BY
       sjh.run_date,
       sjh.run_time

As McNets pointed out there are various ways to retrieve the date you require. It's good to know though that the dates and times are defined as INT in the sys.jobsxxxx tables and not as dates and times as one would expect.

Reference: dbo.sysjobhistory (Transact-SQL)(Microsoft Technet)

Related Question