SQL Server Error Messages

SQL Server Error Messages

The Events and Errors Message Center, located at this Microsoft Web site, is the primary source of information on error messages for SQL Server 2005 and improves the customer experience when searching for information on SQL Server error messages, as well as other Microsoft products. The new Web site enables customers to perform full-text searches and filter results based on product, version, source, and numeric or symbolic identifier. Additionally, the new interface provides support across multiple languages. The Web site will be continually updated with new troubleshooting information on frequently encountered errors.

Future enhancements to the Events and ErrorsMessageCenter will make troubleshooting content available in downloadable form, giving you access to it from a computer that is not connected to the Internet.

Getting Assistance From Others

If you have not found the information you are looking for in the product documentation or on the Web, you can either ask a question in the SQL Server community or request help from Microsoft support.

SQL Server Community

Has links to newsgroups and forums monitored by the SQL Server community. It also lists community information sources, such as blogs and Web sites. The SQL Server community is very helpful in answering questions, although there is no guarantee of an answer.

SQLServerDeveloperCenter Community

This site focuses on the newsgroups, forums, and other community resources that are useful to SQL Server developers.

Microsoft Help and Support

You can use this Web site to open a case with a Microsoft support professional.

Database Engine Events and Errors

This topic provides information about errors and events for the SQL Server 2005 Database Engine.

Understanding Database Engine Errors

Describes the format of Database Engine error messages and explains how to view error messages and return error messages to applications.

Cause and Resolution of Database Engine Errors

Provides an explanation of the error message, possible causes, and any actions you can take to correct the problem.

Understanding Database Engine Errors

Errors raised by the Microsoft SQL Server Database Engine have the attributes described in the following table.

Attribute Description

Error number

Each error message has a unique error number.

Error message string

The error message contains diagnostic information about the cause of the error. Many error messages have substitution variables in which information, such as the name of the object generating the error, is inserted.

Severity

The severity indicates how serious the error is. Errors that have a low severity, such as 1 or 2, are information messages or low-level warnings. Errors that have a high severity indicate problems that should be addressed as soon as possible. For more information about severities, see Database Engine Error Severities.

State

Some error messages can be raised at multiple points in the code for the Database Engine. For example, an 1105 error can be raised for several different conditions. Each specific condition that raises an error assigns a unique state code.

When you are viewing databases that contain information about known issues, such as the Microsoft Knowledge Base, you can use the state number to determine whether the recorded issue is the same as the error you have encountered. For example, if a Knowledge Base Article describes an 1105 error that has a state of 2 and the 1105 error message you received had a state of 3, the error probably has a different cause than the one reported in the article.

A Microsoft support engineer can also use the state code from an error to find the location in the source code where that error code is being raised. This information might provide additional ideas on how to diagnose the problem.

Procedure name

Is the name of the stored procedure or trigger in which the error has occurred.

Line number

Indicates which statement in a batch, stored procedure, trigger, or function generated the error.

All system and user-defined error messages in an instance of the Database Engine are contained in the sys.messages catalog view. You can use the RAISERROR statement to return user-defined errors to an application.

All database APIs, such as the Microsoft .NET Framework SQLClient namespace, ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC), report the basic error attributes. This information includes the error number and message string. However, not all the APIs report all the other error attributes. For more information, see Handling Errors and Messages in Applications.

Information about an error that occurs in the scope of the TRY block of a TRY…CATCH construct can be obtained in Transact-SQL code by using functions such as ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE in the scope of the associated CATCH block. For more information, see Retrieving Error Information in Transact-SQL and Using TRY...CATCH in Transact-SQL.

Examples

The following example queries the sys.messages catalog view to return a list of all system and user-defined error messages in the Database Engine that have English text (1033).

SELECT

message_id,

language_id,

severity,

is_event_logged,

text

FROM sys.messages

WHERE language_id = 1033;

Database Engine Error Severities

When an error is raised by the SQL Server Database Engine, the severity of the error indicates the type of problem encountered by SQL Server.

Levels of Severity

The following table lists and describes the severity levels of the errors raised by the SQL Server Database Engine.

Severity level Description

0-9

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.

Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.

11-16

Indicate errors that can be corrected by the user.

Indicates that the given object or entity does not exist.

A special severity for queries that do not use locking because of special query hints. In some cases, read operations performed by these statements could result in inconsistent data, since locks are not taken to guarantee consistency.

Indicates transaction deadlock errors.

Indicates security-related errors, such as permission denied.

Indicates syntax errors in the Transact-SQL command.

Indicates general errors that can be corrected by the user.

17-19

Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.

Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.

Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs.

Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.

20-25

Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.

Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.

Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged.

Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged.

Indicates that the table or index specified in the message has been damaged by a software or hardware problem.

Severity level 22 errors occur rarely. If one occurs, run DBCC CHECKDB to determine whether other objects in the database are also damaged. The problem might be in the buffer cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database.

If restarting the instance of the Database Engine does not correct the problem, then the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem. For example, if the message reports that the instance of the Database Engine has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.

Indicates that the integrity of the entire database is in question because of a hardware or software problem.

Severity level 23 errors occur rarely. If one occurs, run DBCC CHECKDB to determine the extent of the damage. The problem might be in the cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database.

Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor.

SQL

accessible – доступный

although – хотя

application – приложение, применение

assign – определять, назначать

attempt – попытка

authentication

batch – группа, пакет

cause – причина, вызывать

CHEKDB – проверка базы данных

commit – совершать

consistency – согласованность, связанность

contain – содержать

convert – преобразовать

correct – правильный, исправлять

could – мог (прош. отcan)

current – текущий

damage – повреждение

DBCC (databaseconsistencychecker) – модульконтроля непротиворечивости данных

deadlock -

default – по умолчанию

denied – запрещен

deny– отрицать, отказывать

describe – описывать

description – описание

determine – определять

disabled

downloadable – загружаемый

elapse -

encounter – сталкиваться

engine – двигатель, движок

entity

establish- устанавливать

establish – устанавливать

event – событие

example – пример

exceed – превышать

execution – исполнение

expire– истекать (осроке)

explanation– объяснение

failure– неудача, сбой

fatal – неизбежный

following – следующий

frequently – часто

hint-подсказка, намек

however– однако

inconsistent – несогласованный, несвязанный

indicate – указывать

instance -

instance – мгновение; for ~ - например,

integrity – целостность

invalid -

issue – выпуск, издание

length – длина

maintain – поддерживать (в рабочем состоянии)

message – сообщение

mismatch

movement – движение

occur – случаться, происходить

permission – разрешение

pipe – труба

possible – возможный

primary – первичный

probably – вероятно

query – вопрос, запрос

question – вопрос

raise – поднимать

rarely – редко

reason – причина

refer – относиться, ссылаться

refuse – отказывать

relate – относиться, связываться

related – связанный

remote – удаленный

request – запрос

require – требовать

resolve -

respond – отвечать

resubmit – переподчинить

retrieve

search – поиск

severe – суровый, строгий

severity – серьезность ошибки

since – с тех пор, как; так как

source – источник

state – состояние

statement – утверждение

store – сохранять

substitution – подстановка, замена

successfully –успешно

such – такой

sure – уверенный

surface – поверхность

take – взять

target – цель

transaction –сделка, транзакция, перевод

trigger – триггер, защелка

try– пытаться

useful – полезный

vendor – поставщик, продавец

User-Defined Error Message Severity

sp_addmessage can be used to add user-defined error messages with severities from 1 through 25 to the sys.messages catalog view. These user-defined error messages can be used by RAISERROR. For more information, see sp_addmessage (Transact-SQL).

RAISERROR can be used to generate user-defined error messages with severities from 1 through 25. RAISERROR can reference a user-defined error message stored in the sys.messages catalog view or build a message dynamically. When using the user-defined error message in sys.messages while generating an error, the severity specified by RAISERROR overrides the severity specified in sys.messages. For more information, see RAISERROR (Transact-SQL).

Error Severity and TRY…CATCH

A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.

Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY…CATCH construct.

Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.

For more information, see TRY...CATCH (Transact-SQL).

Retrieving Error Severity

The ERROR_SEVERITY system function can be used to retrieve the severity of the error that caused the CATCH block of a TRY…CATCH construct to be run. ERROR_SEVERITY returns NULL if called outside the scope of a CATCH block. For more information, see ERROR_SEVERITY (Transact-SQL).

Cause and Resolution of Database Engine Errors

This topic contains cause and resolution information for a number of errors related to the Database Engine.

Error 18456 Severity 1

SQL Server 2005 Event 18456 Severity 1

State Description

2 & 5 Invalid UserID

6 Attempt to use a Windows login name with SQL Authentication

7 Login disabled and password mismatch

8 Password mismatch

9 Invalid password

11 & 12 Valid login but server access failure

13 SQL Server service paused

16 User does not have permission to target database or target database not accessible

18 Change password required

MSSQLSERVER_-1

Message Text

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

Explanation

The SQL Server client cannot connect to the server. This error could be caused by one of the following reasons:

The firewall on the server has refused the connection.

A specified SQL Server instance name is not valid.

The SQL Server Browser service (sqlbrowser) is not started.

User Action

To resolve this error, try one of the following actions:

Make sure that you have configured the firewall on the server instance of SQL Server to open the SQL Server Browser port.

Make sure that the SQL Server Browser service is started on the server.

Check the spelling of the SQL Server instance name that is specified in the connection string.

Use the SQL Server Surface Area Configuration tool to enable SQL Server to accept remote connections. For more information about the SQL Server Surface Area Configuration Tool, see Surface Area Configuration for Services and Connections.

MSSQLSERVER_-2

Message Text

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Explanation

The SQL Server client cannot connect to the server. This error could occur because the firewall on the server has refused the connection.

User Action

Make sure that you have configured the firewall on the server instance of SQL Server to accept connections.

MSSQLSERVER_2

Message Text

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

Explanation

SQL Server did not respond to the client request because the server is probably not started.

User Action

Make sure that the server is started.

MSSQLSERVER_53

Message Text

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

Explanation

The SQL Server client cannot connect to the server. This error could occur because either the client cannot resolve the name of the server or the name of the server is incorrect.

User Action

Make sure that you have entered the correct server name on the client, and that you can resolve the name of the server from the client. To check TCP/IP name resolution, you can use the ping command in the Windows operating system.

MSSQLSERVER_107

Message Text

The column prefix '%.*ls' does not match with a table name or alias name used in the query.

Explanation

The select list of the query contains an asterisk (*) that is incorrectly qualified with a column prefix. This error can be returned under the following conditions:

The column prefix does not correspond to any table or alias name used in the query. For example, the following statement uses an alias name (T1) as a column prefix, but the alias is not defined in the FROM clause.

SELECT T1.* FROM dbo.ErrorLog;

A table name is specified as a column prefix when an alias name for the table is supplied in the FROM clause. For example, the following statement uses the table name ErrorLog as the column prefix; however, the table has an alias (T1) defined in the FROM clause.

SELECT ErrorLog.* FROM dbo.ErrorLog AS T1;

If an alias has been provided for a table name in the FROM clause, you can only use the alias to prefix columns from the table.

User Action

Match the column prefixes against the table names or alias names specified in the FROM clause of the query. For example, the statements above can be corrected as follows:

SELECT T1.* FROM dbo.ErrorLog AS T1;