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

AS

SELECT                    JJ.instance_id

                                ,sj.job_id

                                ,sj.name as 'JOB_NAME'

                                ,sjt.step_name as 'STEP_NAME'

                                ,JJ.run_status

                                ,JJ.sql_message_id

                                ,JJ.sql_severity

                                ,JJ.message

                                ,JJ.exec_date

                                ,JJ.run_duration

                                ,JJ.server

                                ,sjt.output_file_name

FROM                       (               SELECT                    ssh.instance_id

                                                                                ,sjh.job_id

                                                                                ,sjh.step_id

                                                                                ,sjh.sql_message_id

                                                                                ,sjh.sql_severity

                                                                                ,sjh.message

                                                                                ,(             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'

                                                                                ,sjh.run_duration

                                                                                ,sjh.retries_attempted

                                                                                ,sjh.server

                                                FROM       msdb.dbo.sysjobhistory sjh

                                                JOIN        (               SELECT    sjh.job_id

                                                                                                ,sjh.step_id

                                                                                                ,MAX(sjh.instance_id) as instance_id

                                                                                FROM       msdb.dbo.sysjobhistory sjh

                                                                                GROUP BY               sjh.job_id

                                                                                                                ,sjh.step_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)

GO

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.

Example:

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 sqlservercentral.com