sysjobhistory "sql_severity" codes

Where can I find out what the different "sql_severity" codes mean in the sysjobhistory table?

Eg what does 0 mean?

I've inherited a report that excludes ones with a 0 as "0 is not a real error"?

I googled and looked at MSDN etc etc.. but no real luck.

From here, the sql_severity column is the "severity of any SQL Server error."

When this column has the value 0, that means either no message was returned (and it's therefore a magic value), or a message with severity 0 was returned. I would argue this is a bad table design, as the column does not allow NULL.

It's almost the same thing with the sql_message_id column. This column will contain 0 when no message was returned -- in this case, though, it's purely a magic value, as there's no message id 0 (run SELECT * FROM sys.messages WHERE message_id = 0;).

You may want to clarify the intent of the report query you've inherited, because only comparing the sql_severity column may not do exactly what the author thought. (Severity 0 errors being raised are undoubtedly rare, but hey, you asked.)

-
If it's supposed to look for steps that returned a message, then it should check for sql_message_id > 0. As mentioned, it's completely possible that a job step can have a sql_severity > 0 and/or sql_message_id > 0 while still succeed. **

-
If the query is supposed to look for failed steps, it should be looking at the run_status column instead.

** You can try this yourself by creating and running a job with a T-SQL step that simply calls RAISERROR.

StackExchange Database Administrators Q#41878, answer score: 2