Listing Failed Jobs in SQL Server

Hello everyone,

In this article, I will give information about listing the failed jobs in SQL Server.

In some cases, we may want to list the jobs that are not running successfully in SQL Server.

You can easily do this with the help of the code below.

SELECT @@SERVERNAME ServerName,
       j.name 'Job Name',
       js.step_name StepName,
       jh.sql_severity ImportanceLevel,
       jh.message Message,
       msdb.dbo.agent_datetime(jh.run_date, jh.run_time) RunTime
FROM msdb.dbo.sysjobs AS j
    INNER JOIN msdb.dbo.sysjobsteps AS js
        ON js.job_id = j.job_id
    INNER JOIN msdb.dbo.sysjobhistory AS jh
        ON jh.job_id = j.job_id
WHERE jh.run_status = 0
      AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time) > GETDATE() - 1
ORDER BY msdb.dbo.agent_datetime(jh.run_date, jh.run_time) DESC;

When you run the code, you will get a result like the one below.

As you can see, unsuccessful jobs are listed.

Good luck to everyone in business and life.