HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

What is the Query to display the failed sql jobs

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thewhatsqlqueryjobsfaileddisplay

Problem

What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query

Solution

I don't think you will really get "all" the job information with one query since jobs can be configured to go to output files. Output files can at times get more information than is reported or written to the msdb tables.

However, the view that is found by looking at the job history via SSMS can be pulled with this query to return only failed jobs (e.g. if a job has 2 steps and the second failed this query will return both steps):

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
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 AND jh.step_id = js.step_id
WHERE jh.run_status = 0

Code Snippets

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
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 AND jh.step_id = js.step_id
WHERE jh.run_status = 0

Context

StackExchange Database Administrators Q#88810, answer score: 19

Revisions (0)

No revisions yet.