Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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