Job duration

• bala2

  SSCertifiable

  Points: 5547

  More actions

  February 4, 2012 at 3:53 am

  #267652

  Hi ,

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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 4, 2012 at 4:33 am

  #1442735

  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]

• sumitagarwal781

  Default port

  Points: 1493

  More actions

  February 4, 2012 at 11:25 am

  #1442766

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

  java

• Express12

  SSCoach

  Points: 18526

  More actions

  August 19, 2013 at 7:02 am

  #1642666

  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

• Express12

  SSCoach

  Points: 18526

  More actions

  August 19, 2013 at 7:19 am

  #1642675

  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