17

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

12

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

| improve this answer | |
8

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 @PreviousDate datetime  
DECLARE @Year VARCHAR(4)   
DECLARE @Month VARCHAR(2)  
DECLARE @MonthPre VARCHAR(2)  
DECLARE @Day VARCHAR(2)  
DECLARE @DayPre VARCHAR(2)  
DECLARE @FinalDate INT  

-- Initialize Variables  
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day   
SET @Year = DATEPART(yyyy, @PreviousDate)   
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))  
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)  
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))  
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)  
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)  

-- 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

| improve this answer | |
  • Thank you for the tip, this is exactly what I was looking for. I think we could simplify the variables a bit to get the same results: DECLARE @FinalDate INT; -- Initialize Variables SET @FinalDate = CAST(CONVERT(VARCHAR(10), DATEADD(dd, -1, GETDATE()), 112) AS INT) -- Yesterday's date as Integer in YYYYMMDD format -- Final Logic .... no change – user100028 Jul 13 '16 at 13:17
1

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
| improve this answer | |

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.