Programmatically finding details about failed SQL Server agent job (using TSQL/Script)

Often professionals use management studio (or third party tool interface) to determine which SQL Server agent job failed in last executing and why it failed at which step. However if you want to get all those information directly from a script (TSQL), use the below procedure:

[Note: This script and procedure can be further modified to incorporate in further automation too. Also this procedure and associated code may be further modified easily to be incorporated in some automation or reporting plan]

Step 1: Create a view (named: dbo.V_FailedJobs) with below code (You can keep this on the server as a tool for your daily activity. In this case, you do not have to recreate this view each time)

CREATE VIEW dbo.V_FailedJobs
SELECT                    JJ.instance_id
                                , as 'JOB_NAME'
                                ,sjt.step_name as 'STEP_NAME'
FROM                       (               SELECT                    ssh.instance_id
                                                                                ,(             CASE sjh.run_status
                                                                                                WHEN 0 THEN 'Failed'
                                                                                                WHEN 1 THEN 'Succeeded'
                                                                                                WHEN 2 THEN 'Retry'
                                                                                                WHEN 3 THEN 'Canceled'       
                                                                                                WHEN 4 THEN 'In progress'
                                                                                END) as run_status
                                                                                ,((SUBSTRING(CAST(sjh.run_date AS VARCHAR(8)), 5, 2) + '/'
                                                                                + SUBSTRING(CAST(sjh.run_date AS VARCHAR(8)), 7, 2) + '/'
                                                                                + SUBSTRING(CAST(sjh.run_date AS VARCHAR(8)), 1, 4) + ' '
                                                                                + SUBSTRING((REPLICATE('0',6-LEN(CAST(sjh.run_time AS varchar)))
                                                                                + CAST(sjh.run_time AS VARCHAR)), 1, 2) + ':'
                                                                                + SUBSTRING((REPLICATE('0',6-LEN(CAST(sjh.run_time AS VARCHAR)))
                                                                                + CAST(sjh.run_time AS VARCHAR)), 3, 2) + ':'
                                                                                + SUBSTRING((REPLICATE('0',6-LEN(CAST(sjh.run_time as varchar)))
                                                                                + CAST(sjh.run_time AS VARCHAR)), 5, 2))) AS 'exec_date'
                                                FROM       msdb.dbo.sysjobhistory sjh
                                                JOIN        (               SELECT    sjh.job_id
                                                                                                ,MAX(sjh.instance_id) as instance_id
                                                                                FROM       msdb.dbo.sysjobhistory sjh
                                                                                GROUP BY               sjh.job_id
                                                                ) AS ssh ON sjh.instance_id = ssh.instance_id
                                                WHERE sjh.run_status <> 1
                                ) AS JJ
JOIN                        msdb.dbo.sysjobs sj
                                ON (jj.job_id = sj.job_id)
JOIN                        msdb.dbo.sysjobsteps sjt
                                ON (jj.job_id = sjt.job_id AND jj.step_id = sjt.step_id)

Step 2:  Whenever you want to find the details, simply query this view and you will get details about the jobs which failed in last executing including the error message.


select * from  dbo.V_FailedJobs

Note: A job will appear in this view only if last execution status is failed.

Note: Concept this automation is taken from