System Function msdb.dbo.agent_datetimeMSDB.dbo.sysjobhistory Table and Run_duration Column


 
In the msdb.dbo.sysjobhistory table, the run_duration column is INT type and stored as (H)HHMMSS format from right to left.
If HH is under 24 hours, we can get the during in time format HH:MM:SS by using a system function. For example:
select msdb.dbo.agent_datetime(19000101, 231353) will return 23:13:53 9 23 hours 13 minutes and 53 seconds.

In very long running job, if the hours go over 24 hours, all the numbers in HHH positions are whole number of hours.

We need to tweak a little to make it work:

  
 
  
 SELECT j.NAME AS 'Job',
       run_date,
       run_time,
       msdb.dbo.Agent_datetime(run_date, run_time) [Run_Datetime] 
,Case when run_duration>24*10000 then 
Cast(run_duration%240000/10000 as varchar(3)) +' (day) '
+ Cast(run_duration/240000 as varchar(2))  
+':' + LefT(Right(Cast(run_duration as varchar(8)),4),2)
+':'+ Right(Cast(run_duration as varchar(8)),2) +''
else
Format(msdb.dbo.agent_datetime(19000101,  run_duration) ,'HH:mm:ss')
end [Duriation in HH:MM:SS]
FROM   msdb.dbo.sysjobs j
       INNER JOIN msdb.dbo.sysjobhistory h
               ON j.job_id = h.job_id
WHERE  j.enabled = 1
ORDER  BY run_duration DESC 

 
Advertisements

3 Comments on “System Function msdb.dbo.agent_datetimeMSDB.dbo.sysjobhistory Table and Run_duration Column”

  1. Thank you Jingyang. Great contribute.
    However, the script is not working properly. I have tested with a variable.

    I built a similar solution, below. Hope it works.
    Thanks

    DECLARE
    @zero_date DATETIME = CAST(0 AS DATETIME)
    — , @run_duration INT = 495948
    — , @run_duration INT = 245948
    , @run_duration INT = 165948

    SELECT
    –debug
    @zero_date AS [@zero_date]
    ,@run_duration AS [@run_duration]
    ,@run_duration / 10000 AS [Hours]
    ,((@run_duration / 10000) / 24) AS [Days]

    , (@run_duration % 10000) AS [mm ss]

    ,CAST(CONVERT(CHAR(8), DATEADD(DAY, ((@run_duration / 10000) / 24), @zero_date), 112) AS INT) AS [@zero_date + Days (integer)]

    –Conversion
    ,(
    CASE
    WHEN ((@run_duration / 10000) /24) > 0
    THEN msdb.dbo.agent_datetime(CAST(CONVERT(CHAR(8), DATEADD(DAY, ((@run_duration / 10000) / 24), @zero_date), 112) AS INT), ((@run_duration % 10000)))
    ELSE msdb.dbo.agent_datetime(19000101, @run_duration)
    END
    ) AS [Run Duration DateTime]

    –Conversion and Formatting
    ,(
    FORMAT( ((@run_duration / 10000) /24), ‘##0’)

    + ‘:’ +

    FORMAT(
    CASE
    WHEN ((@run_duration / 10000) /24) > 0
    THEN msdb.dbo.agent_datetime(CAST(CONVERT(CHAR(8), DATEADD(DAY, ((@run_duration / 10000) / 24), @zero_date), 112) AS INT), ((@run_duration % 10000)))
    ELSE msdb.dbo.agent_datetime(19000101, @run_duration)
    END
    , ‘HH:mm:ss’)
    ) AS [Run Duration Time D HH:mm:ss]

    /********************************************************/

    –DECLARE @run_duration INT = 240000
    /*
    Msg 242, Level 16, State 3, Line 7
    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
    */

    –DECLARE @run_duration INT = 240100
    /*
    Duriation in HH:MM:SS
    0 (day) 1:01:00

    */

    –DECLARE @run_duration INT = 240001
    /*
    Duriation in HH:MM:SS
    0 (day) 1:00:01

    */

    DECLARE @run_duration INT = 250001
    /*
    Duriation in HH:MM:SS
    1 (day) 1:00:01

    */

    SELECT
    –j.NAME AS ‘Job’,
    — run_date,
    — run_time,
    — msdb.dbo.Agent_datetime(run_date, run_time) [Run_Datetime]
    –,
    Case when @run_duration>24*10000 then
    Cast(@run_duration%240000/10000 as varchar(3)) +’ (day) ‘
    + Cast(@run_duration/240000 as varchar(2))
    +’:’ + LefT(Right(Cast(@run_duration as varchar(8)),4),2)
    +’:’+ Right(Cast(@run_duration as varchar(8)),2) +”
    else
    Format(msdb.dbo.agent_datetime(19000101, @run_duration) ,’HH:mm:ss’)
    end [Duriation in HH:MM:SS]
    –FROM msdb.dbo.sysjobs j
    — INNER JOIN msdb.dbo.sysjobhistory h
    — ON j.job_id = h.job_id
    –WHERE j.enabled = 1
    –ORDER BY run_duration DESC

    • Jingyang Li says:

      I don’t know what environment you are using: Here is the core part with variable:
      (I did a quick test on my SQL Server 2014 machine):

      declare @run_duration INT = 495948
      –Set @run_duration = 245948
      –Set @run_duration = 165948

      SELECT
      Case when @run_duration>24*10000 then
      Cast(@run_duration%240000/10000 as varchar(3)) +’ (day) ‘
      + Cast(@run_duration/240000 as varchar(2))
      +’:’ + LefT(Right(Cast(@run_duration as varchar(8)),4),2)
      +’:’+ Right(Cast(@run_duration as varchar(8)),2) +”
      else
      Format(msdb.dbo.agent_datetime(19000101, @run_duration) ,’HH:mm:ss’)
      end [Duriation in HH:MM:SS]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s