SQL Agent reporting failure when all steps suceed The SQL Agent is reporting failure when all the steps under it has succeeded. Anyone else seen this behavior ?
You probably have a job step that has the On success action: set to Quit the job reporting failure as can be seen in the following screen shot:



This can be verified by querying the sys.sysjobhistory and sys.sysjobsteps tables and checking the on_success_action column of the sys.sysjobsteps table. If the column contains a 2 and the step finishes, then it will quit the job and report a failure (Quit the job reporting failure).

Query Job History and Steps

Here is a script for your convenience to query the job steps and history:

SET DATEFORMAT DMY
GO
SELECT sj.name AS Job_Name,
       sjh.step_name AS Step_Name,
       sjh.step_id AS Step_ID,
       sjh.run_status AS [Job_Status],
       STUFF(STUFF(CAST(sjh.run_date as nvarchar(10)),5,0,'.'),8,0,'.') as HistRunDate,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as HistRunTime,
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as HistRunDuration
       --,sjh.run_status AS JobStatus
       ,sjs.last_run_outcome as [Job Step Outcome]
       ,sjs.on_success_action as [On Success: 1=success;2=failure;3=next step]
       ,sjs.on_fail_action as [On Fail: 1=success;2=failure;3=next step]
FROM   dbo.sysjobs           AS sj
       left join dbo.sysjobhistory AS sjh --- was sysjobschedule sjc
            on sjh.job_id = sj.job_id
       left join dbo.sysjobsteps as sjs
            on sjh.job_id = sjs.job_id
            and sjh.step_id = sjs.step_id
WHERE  1=1

ORDER BY
       sjh.run_date desc,
       sjh.run_time desc


Sample Output

...of syspolicy_purge_history job which can be found on most SQL Servers:

Job_Name                | Step_Name                            | Step_ID | Job_Status | HistRunDate | HistRunTime | HistRunDuration | Job Step Outcome | On Success:                     | On Fail:          
                        |                                      |         |            |             |             |                 |                  | 1=success;2=failure;3=next step | 1=success;2=failure;3=next step
------------------------+--------------------------------------+---------+------------+-------------+-------------+-----------------+------------------+---------------------------------+--------------------------------
syspolicy_purge_history | Verify that automation is enabled.   | 1       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:00     | 1                | 3                               | 1                              
syspolicy_purge_history | Purge history.                       | 2       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:00     | 1                | 3                               | 2                              
syspolicy_purge_history | Erase Phantom System Health Records. | 3       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:04     | 1                | 1                               | 2                               


You might have noticed that this job will report a success when the first step fails (On Fail = 1 ). 

References


dbo.sysjobs (Transact-SQL)  
dbo.sysjobhistory (Transact-SQL)  
dbo.sysjobsteps (Transact-SQL)