1

The SQL Agent is reporting failure when all the steps under it has succeeded. Anyone else seen this behavior ?

enter image description here

1
  • 1
    You must have selected quit job reporting failure when job succeeds ?
    – Shanky
    Nov 28, 2017 at 7:27

1 Answer 1

3

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:

Job Step | Advanced : On success quit job reporting failure

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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