What events cause each run_status code to appear in sysjobhistory?

Question

We have been asked to report on the health of our SQL jobs. I am having trouble understanding the run_status column in dbo.sysjobhistory. The MSDB page for sysjobhistory gives this description:

Status of the job execution:

• 0 = Failed
• 1 = Succeeded
• 2 = Retry
• 3 = Canceled

What events cause each status code?

Here is what I have done to attempt to produce each status code. I was able to reproduce...

• 0 (Failed) by writing SQL that throws an error.
  • For example, step 1 is SELECT SELECT
• 1 (Succeeded) by writing SQL that succeeds.
  • For example, step 1 is SELECT 1
• 3 (Cancelled) by stopping a job while it was running
• 4 (In progress) by querying sysjobshistory while a job was running

I know a way produce 0's, 1's, 3's, and 4's, but I don't know all of the ways to produce them, and I have no idea how to cause a 2.

The health of our jobs is binary. We are either healthy or not healthy, because the end user does not want to see anything more granular than that on their dashboard. That is why I am asking this question - I have to interpret the status codes and run durations and draw a single, binary conclusion about the health of the SQL jobs. The problem is that MSDN doesn't explain what the status codes mean, or more importantly, what events might cause them.

For example, if a user cancels a job (resulting in a run_status of 3), we might decide that we don't care and just report that we are healthy. However, if a job gets cancelled because of some other event, we might care about that.

Answer 1

Create a job with a single step. On the advanced tab for that step, change retry attempts from 0 to something greater than 0. In the job step, have it be your SELECT SELECT. Run the job. You will see an entry .

I don't think anyone could provide a full list of all scenarios that could lead to the various possible status values. Did you try anything like this while a job was running (and it's easy to test this with a simple job that has a loop with SELECT TOP (1) name FROM somedb.sys.all_objects;WAITFOR DELAY '00:00:01';); things like:

• unplugging the computer
• failing over (cluster or AG)
• setting somedb to single_user or offline
• killing sqlservr.exe process or shutting down the agent service
• having the step raising batch- and connection-aborting errors
• having the step raise an exception that causes "severe error" or assertion check failures (assuming there are some that exist and you can repro on your version)

run_status is useful, but you can get a better picture from the combination of run_status and message (and, in severe cases, the SQL Server error log). The trouble with message is that the contents can get truncated, so if you have a step that performs 400 successful backups and then one fails, the content will be full of successful messages, and the failure will be missing. For important jobs you may want to log to a table or to a file; you can see a variety of options on the Advanced tab of any job step.

Answer 2

We have in all our jobs a final step that checks all previous job steps if one caused an error and then triggers the notification. However when migrating to SQL2014 I suddenly had this step success when running alone, as soon as it had previous steps (all successful) it failed. Little more searching showed that it was caused by the step that executes a SSIS package, that step always has run_status = 4 in sysjobhistory (which itself makes no sense), looking at the offical documentation showed only that MSFT doesn't do their job of having a proper documentation. (even though users already in 2013 reported this to them). That runstatus = 4 disappears if I got to the step properties and don't set "include step output in history". Seems during migration we set that as new default. I feel MSFT is not doing a good job here and looking at how old this is it seems they don't care at all (They still talk about pagers in the operators :D)