How to find Jobs that ran between two times
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)
You should be able to get this information from msdb.dbo.sysjobhistory table.
Have a look at MS-Docs about it.
This is the sample you'll find there:
SELECT sj.name,
sh.run_date,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) '
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id;
| name | run_date | run_time | run_duration (DD:HH:MM:SS) |
|--------------------------------------------|----------|----------|----------------------------|
| C88FC48A-1F9B-42CB-8D6B-9DB6E394A08F | 20170601 | 08:00:01 | 00:00:00:00 |
| 25C346F3-C83E-4DAC-9A31-C0A0C8C4B862 | 20170601 | 08:00:01 | 00:00:00:00 |
| C88FC48A-1F9B-42CB-8D6B-9DB6E394A08F | 20170601 | 08:00:00 | 00:00:00:01 |
| 25C346F3-C83E-4DAC-9A31-C0A0C8C4B862 | 20170601 | 08:00:00 | 00:00:00:01 |
| Copia de seguretat cada 12 hores.Subplán_1 | 20170601 | 12:00:00 | 00:00:01:50 |
| Copia de seguretat cada 12 hores.Subplán_1 | 20170601 | 12:00:00 | 00:00:01:50 |
| 9D523764-6C16-4F35-A623-B0F4AB0DC071 | 20170601 | 15:18:00 | 00:00:00:00 |
| 9D523764-6C16-4F35-A623-B0F4AB0DC071 | 20170601 | 15:18:00 | 00:00:00:00 |
| 3749C767-EB7A-44EB-B1C2-3D2CB04A2B43 | 20170601 | 21:00:00 | 00:00:00:00 |
| 3749C767-EB7A-44EB-B1C2-3D2CB04A2B43 | 20170601 | 21:00:00 | 00:00:00:00 |