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.

Advertisements

2 thoughts on “SQL Query for retrieving job history

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