October 10, 2012

SQL SERVER: T-SQL to display Job history

We have multiple jobs running on the server as per the schedules. We need to have the status of the each job at the end of the day, so we can know that which jobs runs successfully and which gets failed.

We have found the table, sysjobhistory, which contains history and get the list as per oyur requirements. Let me share the query:

SELECT
    j.name As JobName,
    run_date As RunDate,
    RIGHT('000000' + CONVERT(varchar(6), Run_Time), 6) AS RunTime,
    CASE h.run_Status   
        WHEN  0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN  'Retry'
        WHEN 3 THEN 'Cancelled'
    ELSE ''
    END AS JobStatus,
    RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) As JobDurationinHHMMSS
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j on h.job_id = j.job_id
    AND h.step_id = 0
ORDER BY j.Name, h.run_date

Here, we have used “h.step_id = 0”, as we just concern about SQL server job status, we are not concerned about each step of the job. This query gives us result as follows:

SQL Yoga T-SQL to display job history


We have also configured, Notification email, which sends an email whenever job is getting failed. This query is used to review all job statuses together. Please let me know your comments

Reference: Tejas Shah (
http://www.SQLYoga.com)

No comments:

Post a Comment