What is the Query to display the failed sql jobs

Question

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

Answer 1

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

Answer 2

You can get this information by the standard reports available in sql server:

Make a right click on SQL server agent> Standard reports and select the desired report like job executions "failed ones or the ones taking lot of time" ..Select the report as per you're need:

Or,

You can use below script to find jobs failed in last 24 hours:

-- Variable Declarations 
DECLARE @FinalDate INT;
SET @FinalDate = CONVERT(int
    , CONVERT(varchar(10), DATEADD(DAY, -1, GETDATE()), 112)
    ) -- Yesterday's date as Integer in YYYYMMDD format
-- Final Logic
SELECT  j.[name],

        s.step_name,

        h.step_id,

        h.step_name,

        h.run_date,

        h.run_time,

        h.sql_severity,

        h.message,

        h.server

FROM    msdb.dbo.sysjobhistory h

        INNER JOIN msdb.dbo.sysjobs j

            ON h.job_id = j.job_id

        INNER JOIN msdb.dbo.sysjobsteps s

            ON j.job_id = s.job_id 
                AND h.step_id = s.step_id

WHERE    h.run_status = 0 -- Failure

         AND h.run_date > @FinalDate

ORDER BY h.instance_id DESC;

And if you need the information as a report use the code in this link

Answer 3

I modified Shawn Melton's code slightly to get all job errors in the last 24 hours. There was a statement missing in the join, I fixed that as well. Thanks much Shawn, great stuff!

/*  Select all jobs with an error in the past 24 hours */
SELECT MSDB.dbo.agent_datetime(jh.run_date,jh.run_time) as date_time
    ,j.name as job_name,js.step_id as job_step,jh.message as error_message
    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 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

Answer 4

SELECT  [JobName] = [jobs].[name]
 ,[Category] = [categories].[name]
 ,[Owner] = SUSER_SNAME([jobs].[owner_sid])
 ,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
 ,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
 ,[Description] = [jobs].[description]
 ,[Occurs] = 
 CASE [schedule].[freq_type]
 WHEN   1 THEN 'Once'
 WHEN   4 THEN 'Daily'
 WHEN   8 THEN 'Weekly'
 WHEN  16 THEN 'Monthly'
 WHEN  32 THEN 'Monthly relative'
 WHEN  64 THEN 'When SQL Server Agent starts'
 WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
 ELSE ''
 END
 ,[Occurs_detail] = 
 CASE [schedule].[freq_type]
 WHEN   1 THEN 'O'
 WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
 WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
 LEFT(
 CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
 LEN(
 CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
 CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
 ) - 1
 )
 WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
 WHEN  32 THEN 'The ' + 
 CASE [schedule].[freq_relative_interval]
 WHEN  1 THEN 'First'
 WHEN  2 THEN 'Second'
 WHEN  4 THEN 'Third'
 WHEN  8 THEN 'Fourth'
 WHEN 16 THEN 'Last' 
 END +
 CASE [schedule].[freq_interval]
 WHEN  1 THEN ' Sunday'
 WHEN  2 THEN ' Monday'
 WHEN  3 THEN ' Tuesday'
 WHEN  4 THEN ' Wednesday'
 WHEN  5 THEN ' Thursday'
 WHEN  6 THEN ' Friday'
 WHEN  7 THEN ' Saturday'
 WHEN  8 THEN ' Day'
 WHEN  9 THEN ' Weekday'
 WHEN 10 THEN ' Weekend Day' 
 END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
 ELSE ''
 END
 ,[Frequency] = 
 CASE [schedule].[freq_subday_type]
 WHEN 1 THEN 'Occurs once at ' + 
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
 WHEN 2 THEN 'Occurs every ' + 
 CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
 WHEN 4 THEN 'Occurs every ' + 
 CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
 WHEN 8 THEN 'Occurs every ' + 
 CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
 ELSE ''
 END
 ,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
 ,[Next_Run_Date] = 
 CASE [jobschedule].[next_run_date]
 WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
 ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
  STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
 END
FROM  [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
  LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
  ON [jobs].[job_id] = [jobschedule].[job_id] 
  LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
  ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
  INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK) 
  ON [jobs].[category_id] = [categories].[category_id] 
  LEFT OUTER JOIN 
 ( SELECT  [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + 
 (([run_duration] % 10000) / 100 * 60) + 
  ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
 FROM  [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
 WHERE  [step_id] = 0 
 GROUP BY [job_id]
  ) AS [jobhistory] 
  ON [jobhistory].[job_id] = [jobs].[job_id];
GO