T-SQL statement to return SQL Job Last Run Date

  • Word of warning : if the history has been purged/deleted then sysjobhistory will not display the same data as "Job Activity Monitor" i.e. "Job Activity Monitor" will display a last run date but this is not obtainable from running queries against sysjobhistory where the history has been purged because it just won't exist.

    Use sysjobactivity to obtain the "real" last run date and to be in agreement with "Job Activity Monitor".

    Try it; create a test job, run it a few times, manually delete the latest history records then run your query using sysjobhistory. You will find that your queries report an earlier last run time than SQL's "Job Activity Monitor".

    Does it matter ? Well it may do, I noticed this problem when deciding which jobs could be deleted. It seemed that some jobs were never run but this was not the case as the history had been deleted.

  • Do you have the script please to use sysjobactivity?

    I would like to bring all the jobs with their names, steps, and the status, last run time, even if there is no history, or the jobs is disabled, not scheduled

  • Bringing a thread back from the dead but to help people that stumble accross this page in the future

    you could use the sql system function msdb.dbo.agent_datetime(run_date, run_time) to calculate the date time value from the run_date and run_time integer values stored in the msdb.dbo.sysjobhistory table.

    e.g.

    Select J.name,MAX(msdb.dbo.agent_datetime(run_date, run_time)) as 'LastLogRun'

    FROM msdb.dbo.sysjobhistory H

    Inner join msdb.dbo.sysjobs J ON H.job_id = J.job_id

    where step_id = 0

    GROUP BY J.name

  • This returns runtime information:
    SELECT
        CAST(STR(h.run_date, 8, 0) AS DATE) AS StartDate
    ,   SUBSTRING(CAST(CAST(CAST(STR(h.run_date, 8, 0) AS DATETIME)
                  + CAST(STUFF(STUFF(RIGHT('000000'
                                           + CAST (h.run_time AS VARCHAR(6)), 6),
                                     5, 0, ':'), 3, 0, ':') AS DATETIME) AS TIME) AS VARCHAR(16)),
                  1, 8) AS StartTime
    ,   STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0,
              ':') AS Run_Duration
    ,   j.name JobName
    ,   h.step_id StepNumber
    ,   h.step_name StepName
    ,   CAST(STR(h.run_date, 8, 0) AS DATETIME)
        + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6), 5,
                           0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
    ,   DATEADD(SECOND,
                ( ( h.run_duration / 1000000 ) * 86400 ) + ( ( ( h.run_duration
                                                                 - ( ( h.run_duration
                                                                  / 1000000 )
                                                                  * 1000000 ) )
                                                               / 10000 ) * 3600 )
                + ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
                      / 100 ) * 60 ) + ( h.run_duration - ( h.run_duration / 100 )
                                         * 100 ),
                CAST(STR(h.run_date, 8, 0) AS DATETIME)
                + CAST(STUFF(STUFF(RIGHT('000000'
                                         + CAST (h.run_time AS VARCHAR(6)), 6), 5,
                                   0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
    ,   CASE h.run_status
          WHEN 0 THEN 'failed'
          WHEN 1 THEN 'Succeded'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancelled'
          WHEN 4 THEN 'In Progress'
        END AS ExecutionStatus
    ,   h.message MessageGenerated
    FROM
        msdb.dbo.sysjobhistory h
    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
    WHERE
        h.step_id > 0 -- eliminates job total information row
    ORDER BY
        StartDate ASC
    ,   StartTime ASC;

Viewing 4 posts - 16 through 18 (of 18 total)

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