Blog Detail

SQL SERVER T-SQL to display Job history
27 Oct 2012
Tejas Shah
/Project/ImageURL/SQL Yoga T-SQL to display job history_thumb[1].png
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:
 
We have also configured, Notification email, which sends an email whenever a job is getting failed. This query is used to review all job statuses together. Please let me know your comments
 

Tags

SQLYoga Tejas Shah SQL Server SQL Job

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Read More

Categories

Recent Post