Post reply

Jobs that run during specific duration

  • September 26, 2013 at 9:40 am

    #277579

    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

    #1653670

    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

    "I cant 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."

    -- 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