SQL ERROR Messages
Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error. The error severity levels provide a quick reference for you about the nature of the error. The error state number is an integer value between 1 and 127; it represents information about the source that issued the error. The error message is a description of the error that occurred. The error messages are stored in the sysmessages system table.
We can see all the system messages running following statement in query analyzer.
SELECT * FROM master.dbo.sysmessages
The severity level are displayed in the table below.
| 0 to 10 | Messages with a severity level of 0 to 10 are informational messages and not actual errors. |
| 11 to 16 | Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, the error message returned in the invalid update query, used earlier, had a severity level of 16. |
| 17 | Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner. |
| 18 | Severity level 18 messages indicate nonfatal internal software problems. |
| 19 | Severity level 19 indicates that a nonconfigurable resource limit has been exceeded. |
| 20 | Severity level 20 indicates a problem with a statement issued by the current process. |
| 21 | Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database. |
| 22 | Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take. |
| 23 | Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands. |
| 24 | Severity level 24 indicates a hardware problem. |
| 25 | Severity level 25 indicates some type of system error. |
Reference : Pinal Dave (https://blog.sqlauthority.com) , MSDN Error Severity Level
22 Comments. Leave new
Hi,
Thank you very much, this description is really very helpful and also thanx for the msdn reference link.
Please also give error state description. As you previously wrote that error state number is an integer ranges from 1 to 127, now i want to ask that plz give some idea about when one should use error state = 1 or error state = 2 …..error state = 127. As a beginner how we get to know that this error state is used for this error or wutever. Actually i saw many examples on net , i which mostly error state = 1 is used but in some other examples error state = 2 or error state = 5 is used. Now i’m unable to figure out, each of these numbers are related with which type of errors.
-Thanx in advance.
Error state is basically to differentiate between the same error message being thrown from different spots.
A bad example:
Say you check for a positive number X at the beginning of the procedure. If it’s negative you throw “X is negative” with state 1.
At the end of your procedure, you check X again and if it’s negative you throw “X is negative” and set the state to any number 2-127 just so that if you were to look it up in an errors listing that you know if it’s state 1 it’s because X started negative, if it’s state 2 it’s because X ended negative.
In most cases you will change your text string but you may want the string to represent the type of error and the state to differentiate between each place the error may be generated.
In short I cant think of a useful application and it will almost always be the same number.
Hi Pinal
How can we get the error message captuerd in a variable ,
i am getting the error number but i need the exact error message.
For example while doing to bulk insert i am getting truncation
error at line number 1 and column number 2. I want this to be captured in variable. Is that possible.
Thanks,
Prasanna
hi..
can you please clear this doubt.
wheather the error message is recorded somewhere in sql server
for eg: if i am excuting insert query and it returns an error like ‘ incorrect syntax error’
is it is recorded somewhere
if yes..? where..?
Hi Pinal,
How can i generate error base on serverity or error number.
I have tried but could not generate alert.
Hi Pinal
what is the purpose of is_event_logged column in sys.messages table.
For example
if i set the value to 1 , will the error message will be logged in error log.
if it is 0, where the error messages will be logged?
Regards
Kokila K
Correct
1 => Also log to eventvwr (in addition to sql erro logs)
0 => Only sql logs.
get a test server.
downlaod the eval edition
dirty your hands :-)
Severity 16:
What does exactly mean “can be fixed by the user.” ???
Namely, i’ve got the following error during a very large script execution:
Error: 17053, Severity: 16, State: 1.
C:\XYZLOG1.LDF: Operating system error 112(There is not enough space on the disk.)
My questions are:
1) Can we tell whether some data was or wasn’t written to database? And we need to reaply the script, eventually?
2) Why the severity was 16, not 17 ???
We have run out of the resources in this case!
Hello Pinal,
I have one requirement for error handling where I want to capture a particular record that caused the error and due to which column the error occured.
For Example
If my destination column is int and I am trying to insert a char value from the source table. In this case I want to find out the column and name of the source table and that particular record(any column value for that record)
Please let me know your suggestion on this.
Hi,
I need a help on sql sever error log.
Problem Description:
My production site was down because a simple select query was taking too much time and sql server connection time out throws.
Indexes are there on the table but while investigating we found the table scan due to the query was taking time,so what we did we create another index and it worked out.
My Question:
How I will get the error report or log in sql server.
Note: I read in some blog sql server only maintain log for last 6 session.
Please provide me the table name where I will get the correct root cause on a particular date.
Pinal,
Your articles are always useful and highly precise.
Thanks for sharing your Knowledge!
I would also like to mention an older article on this subject:
http://www.sommarskog.se/error-handling-I.html
Hi, I got an error 9003, severity 17, state 1. Can’t find out what resource caused this error. Need help, please.
This error is happened when log shipping failed.
hello sir
i am new In sqlservver Quiry. i am getting error on on update a result:
“Error 50013, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. “
Hi,
I have a dumb question here, On my SQL 2008 instance, the error message doesn’t read “Server: Msg 245”, instead it just reads “Msg 245” etc. Notice the “Server:” missing. Is there a way i can get it to add the server:? This is needed for a validation being done with our code.
Alternate ? on the Topic of SYS.Messages… We create custom messages in sys.messages for each specific Customer/utilization purpose. is there a way to maintain these messages Specifically for the Database “NAME”,BrokerID or other method when we bring back the backups to our central server.(Other then Multiple instances)
Thanks
Alan C
Hi Pinal,
We have received the below error on our SQL Server Errorlog (SQL 2008 R2)
Could you please advice on what could be the problem?
Error: 17836, Severity: 20, State: 14.
Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 163.164.107.201]
Hi Renuka,
Same error i had got on my one of the production server..which was actually a brute force attack …if you can check your error log you will see there are number of login attempt through sa, sqladmin or probe.
I have a problem, the error is the following:
Error Number: 1088 -ErrorMessage: Cannot find the object “%table name%” because it does not exist or you do not have permissions
The thing is that the user that executes the stored procedure that threw this error message is owner of the database.
I tried to replicate the error and then, the stored procedure executed without problems.
Do you have any idea what could have happened? What can be the cause of this error message???
Thanks
RAISEERROR & XP_EVENTLOG write into Windows Eventlog with eventids (17061 or 17063 only). Is there a way in SQL to change these eventids, or do we need other programming?
How to set Msg 201 error code in Raise Error funtion