Job duration

  • Hi ,

    Can any one tell me how to know the duration of job that was run last time.

  • Have a look at sp_help_jobactivity.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • You can use job history to find the duration of job. by right clicking on job in agent activity monitor.

  • Anyone have a "working query" to render AVG run_duration for a specific Jobname in SQL 2008 R2? I cannot seem to get this query working:

    select

    j.name as 'Jobname',

    Replicate('0',(6-len(Cast(Avg(jh.run_duration) as varchar(6))))) + Cast(avg(jh.run_duration) as varchar(6)) as 'AVG_Runtime'

    FROM msdb.dbo.sysjobhistory jh

    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id

    WHERE j.enabled = 1 --Only Enabled Jobs

    and J.name = 'MY_Specific_Jobname_here'

    group by jh.job_id, Replicate('0',(6-len(Cast(Avg(jh.run_duration) as varchar(6))))) + Cast(avg(jh.run_duration) as varchar(6)) as 'AVG_Runtime'

    thx for any help!

    BT
  • This seems to work:

    use msdb;

    GO

    select

    d.jobname

    ,d.servername

    , avgDurationMinutes=avg(d.durationMinutes)

    , daydate=convert(char(10),startdatetime,101)

    from (

    select

    jobname=j.name

    ,servername=server

    ,startdatetime=

    CONVERT (DATETIME, RTRIM(run_date))

    + (

    run_time * 9

    + run_time % 10000 * 6

    + run_time % 100 * 10

    ) / 216e4

    , durationMinutes=

    (CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60

    + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60

    + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)

    )/60.

    ,enddatetime =

    dateadd

    (ss,

    (CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60

    + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60

    + CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)

    )

    ,

    (CONVERT (DATETIME, RTRIM(run_date))

    + (

    run_time * 9

    + run_time % 10000 * 6

    + run_time % 100 * 10

    ) / 216e4 )

    )

    , retries_attempted

    from sysjobs j (nolock)

    join sysjobhistory h on

    h.job_id = j.job_id

    and h.step_id = 0 -- look only at the job outcome step for the total job runtime

    where

    j.name in ('CSG Replication Slot 1') -- Set the jobname here

    --j.name in ('<strong>JobName</strong>') -- Set the jobname here

    ) d

    where

    datepart(dw,startdatetime)=7 -- Set your day of week here if desired. 7=Saturday

    group by

    d.jobname

    ,servername

    ,convert(char(10),startdatetime,101)

    order by

    d.jobname

    ,servername

    ,cast(convert(char(10),startdatetime,101)as datetime) desc

    BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply