20

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

1
15

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

enter image description here

10

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

0
2

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
-2
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
1
  • 2
    you should add some text, to explain your answer. – nbk Nov 24 '20 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.