Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Jobs that run during specific duration Expand / Collapse
Posted Thursday, September 26, 2013 9:40 AM



Group: General Forum Members
Last Login: Tuesday, January 26, 2016 6:59 AM
Points: 1,606, Visits: 1,595
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.

//All our dreams can come true, if we have the courage to pursue them//
Post #1498934
Posted Thursday, September 26, 2013 11:01 AM



Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 2,153, Visits: 7,254
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
SELECT AS job_name,
j.[description] AS job_description,
jh.step_id AS step,
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,
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
FROM job_step_history
WHERE run_date>=@startDate
AND run_date<=@endDate

-- Alan Burstein

Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Post #1498966
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse