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.
You can find the list of severity codes on MSDN here: Database Engine Error Severities
As well zero (0) is marked as not a real error because as stated in the link above:
Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
