September 26, 2013 at 9:40 am
Hi,
Do you have any handy script where I can get the list of jobs that ran during specified time period on a particular day.
For example, I need the job details that were running on 25th Sep 2013 between 2AM to 3AM. It is not necessary that job has to run from 2AM to 3 AM. The job may get completed/failed at 2.05 or the job might have started at 2.55. All the jobs that ran any time during this time span has to be listed.
Please help.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
September 26, 2013 at 11:01 am
Below is a script that will get you information about all the jobs that ran between a two dates. I was not able to figure out how to get you between specific times (I only had a few minutes to put this together). Nonetheless, this should get you a closer to what you are looking for...
USE msdb
DECLARE @startDate date='9/1/2013',
@endDate date='10/1/2013';
WITH job_step_history AS
(
SELECTj.name AS job_name,
j.[description] AS job_description,
jh.step_id AS step,
jh.step_name,
js.command AS step_command,
CONVERT(datetime,CAST(jh.run_date AS char(8))) AS run_date,
left(replace(right(convert(varchar,dateadd(millisecond,jh.run_time,'00:00:00:000'),21),12),'.',':'),11) AS run_time,
jh.run_duration
FROM sysjobhistory jh
JOIN sysjobs j ON jh.job_id=j.job_id
LEFT JOIN sysjobsteps js ON jh.job_id=js.job_id AND jh.step_id=js.step_id
)
SELECT *
FROM job_step_history
WHERE run_date>=@startDate
AND run_date<=@endDate
-- Itzik Ben-Gan 2001
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply