SQL Query for retrieving job history

Hi Folks,

Today,I am sharing a very useful query which is often required by most of the DBA’s and Developer’s

who want an alternative and pretty useful one for finding results for current day (day of execution).

Query does some general string conversions and calculations.

use msdb
SELECT
h.job_id
,j.name
,CAST( SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 5,2) +’-‘
    + SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 7,2) +’-‘
    + SUBSTRING(CONVERT(VARCHAR(10),h.run_date),1,4) + ‘ ‘ +
    + SUBSTRING(CONVERT(VARCHAR(10),replicate(‘0’,6-len(h.run_time)) + CAST(h.run_time AS VARCHAR)), 1, 2) + ‘:’
    + SUBSTRING(CONVERT(VARCHAR(10),replicate(‘0’,6-len(h.run_time)) + CAST(h.run_time AS VARCHAR)), 3, 2) + ‘:’
    + SUBSTRING(CONVERT(VARCHAR(10),replicate(‘0’,6-len(h.run_time)) + CAST(h.run_time AS VARCHAR)), 5, 2)  AS SMALLDATETIME)
    AS JobStart
,DATEADD(SECOND, CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24)
    + (h.run_duration / 10000 % 100) * 3600
    + (h.run_duration / 100 % 100) * 60
    + (h.run_duration % 100) ELSE 0 END,CAST( SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 5,2) +’-‘
    + SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 7,2) +’-‘
    + SUBSTRING(CONVERT(VARCHAR(10),h.run_date),1,4) + ‘ ‘ +
    + SUBSTRING(CONVERT(VARCHAR(10),replicate(‘0’,6-len(h.run_time)) + CAST(h.run_time AS VARCHAR)), 1, 2) + ‘:’
    + SUBSTRING(CONVERT(VARCHAR(10),replicate(‘0’,6-len(h.run_time)) + CAST(h.run_time AS VARCHAR)), 3, 2) + ‘:’
    + SUBSTRING(CONVERT(VARCHAR(10),replicate(‘0’,6-len(h.run_time)) + CAST(h.run_time AS VARCHAR)), 5, 2)  AS SMALLDATETIME))
    AS JobEND
,outcome = CASE
        WHEN h.run_status = 0 THEN ‘Fail’
        WHEN h.run_status = 1 THEN ‘Success’
        WHEN h.run_status = 2 THEN ‘Retry’
        WHEN h.run_status = 3 THEN ‘Cancel’
        WHEN h.run_status = 4 THEN ‘In progress’
    END

FROM sysjobhistory AS h
JOIN sysjobs AS j
    on j.job_id = h.job_id
WHERE
    h.step_id = 0
AND j.enabled = 1
AND CAST(SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 5,2) +’-‘
    + SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 7,2) +’-‘
    + SUBSTRING(CONVERT(VARCHAR(10),h.run_date),1,4) AS SMALLDATETIME) = CONVERT(VARCHAR(10), GETDATE(), 121)

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.