Tuesday, February 7, 2012

Job step wise duration counts

select step_id [Step No],step_name [Step Name],case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration
   FROM (select step_id,step_name, CASE
          WHEN run_duration > 0
          THEN
               (run_duration / 1000000) * (3600 * 24)
             + (run_duration / 10000 % 100) * 3600
             + (run_duration / 100 % 100) * 60
             + (run_duration % 100)
          ELSE
             NULL
       END AS LastRunDuration from msdb.dbo.sysjobhistory where job_id='A09DCA56-E235-4A87-A3D7-C3BB9D823F20' and run_status=1 and step_id <> 0
   and run_date >= (select MAX(run_date) from msdb.dbo.sysjobhistory where step_name='CHECK_job_to_complete')
   ) T
  

No comments:

Post a Comment