Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:21 AM
Points: 1,602, Visits: 1,572
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//
Post #1498934
Posted Thursday, September 26, 2013 11:01 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:41 AM
Points: 617, Visits: 2,893
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 j.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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1498966
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse