Chapter 13 - Resolving System Error Messages

Article
01/28/2010

Identified in the following section are System error messages for which additional information or user action is provided.

Error 103

Severity Level 15

Message Text

The %S_MSG that starts with '%.*ls' is too long. Maximum length is %d.

Explanation

If you enclose a character string in double quotation marks that is more than 128 characters, the application may receive this error. When the QUOTED_IDENTIFIERS option is set on (SET QUOTED_IDENTIFIERS ON), Microsoft SQL Server expects quoted identifiers to be enclosed in double quotation marks (") and data values to be enclosed in single quotation marks ('). In the case of character parameters of stored procedures, data values enclosed in double quotation marks are accepted by SQL Server if the character string is less than 128 characters. They should be considered syntax errors by SQL Server and generate an error.

You can also see this in ODBC applications using the SQL Server ODBC driver versions 2.50.0121 and later. These drivers set QUOTED_IDENTIFIERS ON when run against a SQL Server version 6.x or later server so that the driver's behavior more closely matches the ANSI and ODBC standards. ODBC applications which use double quotation marks for parameter values may see this behavior after you upgrade to SQL Server 6.x or later and the ODBC 2.50.0121 or later driver.

This behavior has been seen when using stored procedures that contain nested stored procedure execution with parameters delimited in double quotation marks (such as xp_cmdshell) from ODBC applications such as Microsoft Internet Information Service (IIS).

SQL Server does not always flag stored procedure parameters enclosed in double quotation marks if the SET QUOTED_IDENTIFIER ON option has been issued.

Action

Change the procedure call to enclose the parameter values in single quotation marks:

EXECUTE myproc 1, 'abcdefghijklmn'

ODBC applications can also use bound parameter markers instead of providing the values directly using either proprietary Transact-SQL syntax:

SQLExecDirect(hstmt, "EXECUTE myproc ?,?", SQL_NTS);

or, the ODBC standard syntax:

SQLExecDirect(hstmt, "{ call myproc (?,?)}", SQL_NTS);

In these cases, you may be able to work around the problem by placing a SET QUOTED_IDENTIFIER OFF statement at the beginning of the stored procedure. This setting will be in effect only for the scope of the stored procedure and will not affect other statement execution outside of the stored procedure.

CREATE PROCEDURE iisproc 
AS
BEGIN
DECLARE @stmt varchar(255)
SET QUOTED_IDENTIFIER OFF
SELECT @stmt = 'xp_cmdshell "c:\myprog
xxxxxxxxxxxxxxxxxxxxxxx"'
EXECUTE (@stmt)
END

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"EXECUTE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SET QUOTED_IDENTIFIER" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Using Identifiers" in Microsoft SQL Server Database Developer's Companion

Error 107

Severity Level 15

Message Text

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

Explanation

A column prefix was specified that does not correspond to any table name specified in the query. Match the column prefixes against the table names and alias names in the FROM clause.

One common cause of this error is the use of a table name when an alias name for the table is also supplied. When working with a table alias (a correlation name in ANSI terminology), the syntax checking in Microsoft SQL Server complies with the ANSI specification. ANSI states,

A <table name> ... is exposed ... if and only if the <table reference>
does not specify a <correlation name>.

If an alias has been provided for a table name in the FROM clause, you can only use the alias to qualify columns from the table; the table name cannot be used elsewhere in the statement because they are flagged as syntax errors.

As an example of the difference in behavior, assume this script has been executed:

USE Northwind
GO
SELECT Customers.ContactName
FROM Customers cu
WHERE ContactName LIKE 'C%'
GO
SELECT cu.ContactName
FROM Customers cu
WHERE Customers.ContactName LIKE 'C%'
GO

In both SELECT statements, notice the use of Customers to qualify the column ContactName even though a table alias of cu has been provided to substitute for the table name. Both of these queries return this error message:

Server: Msg 107, Level 16, State 3
The column prefix 'Customers' does not match with a table name or alias name used in the query.

Action

Use the column prefix that corresponds to the exposed name of the table.

Rewrite any queries where column names are qualified with the table name. Use the table alias instead. For example, this SELECT statement is equivalent to the ones above and uses a table alias for column qualification:

USE Northwind
GO
SELECT cu.ContactName
FROM Customers cu
WHERE cu.ContactName LIKE 'C%'
GO

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"Query Fundamentals" in Microsoft SQL Server Database Developer's Companion

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Using Table Aliases" in Microsoft SQL Server Database Developer's Companion

Error 109

Severity Level 15

Message Text

There are more columns in the INSERT statement than values specified in
the VALUES clause. The number of values in the VALUES clause must match
the number of columns specified in the INSERT statement.

Explanation

This error occurs when more columns are listed in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Do not confuse error 109 with operating-system error 109. Operating-system error 109 means that a named pipe connection has been terminated.

Action

Rewrite the INSERT statement, ensuring that the number of columns specified matches the number of columns in the VALUES clause. For example:

INSERT t1 (col1,col2,col3) VALUES (val1,val2,val3)

See Also

In Other Volumes

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 137

Severity Level 15

Message Text

Must declare the variable '%.*ls'.

Explanation

This error occurs when a variable is used in a SQL script without first declaring the variable. This example returns error 137:

SET @mycol = 'ContactName'
SELECT @mycol
GO

One of the more complicated causes of this error includes the use of a variable that was declared outside the EXECUTE statement. For example:

USE Northwind
GO
DECLARE @mycol nvarchar(20)
SET @mycol = 'ContactName'
EXECUTE ('SELECT @mycol FROM Customers')

Action

Verify that any variables used in a SQL script are declared before being used elsewhere in the script.

Rewrite the procedure so that it does not reference variables in the EXECUTE statement that were declared outside of it.

USE Northwind
GO
DECLARE @mycol nvarchar(20)
SET @mycol = 'ContactName'
EXECUTE ('SELECT ' + @mycol + ' FROM Customers')

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"DECLARE @local_variable" in Microsoft SQL Server Transact-SQL and Utilities Reference

"EXECUTE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SELECT @local_variable" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SET @local_variable" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 156

Severity Level 15

Message Text

Incorrect syntax near the keyword '%.*ls'.

Explanation

This error indicates that the syntax of a Transact-SQL statement is incorrect and that the syntax error was detected near the keyword specified in the error message. The most frequent causes for syntax errors are misspellings of Transact-SQL keywords or operators, and specifying the syntax of a Transact-SQL statement in the wrong order.

One of the more complicated causes for this error may be a compatibility level mismatch for the current database. If the current database has a compatibility level other than 70, SQL Server will not recognize any of the keywords that a database with a compatibility level of 70 would recognize.

Action

First, check the Transact-SQL statement syntax near the keyword specified in the error message. Because Transact-SQL language syntax can be very complex, Microsoft SQL Server may report the syntax error later in the Transact-SQL statement syntax than it actually occurred. Second, reexamine the entire Transact-SQL statement that generated the error. Verify the syntax order of the statement.

Ensure that the database does not have a compatibility level of 65 and has a compatibility level of 70.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"Backward Compatibility" in Microsoft SQL Server Introduction

"Getting Started with Transact-SQL" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Reserved Keywords" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_dbcmptlevel" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 170

Severity Level 15

Message Text

Line %d: Incorrect syntax near '%.*ls'.

Explanation

This error indicates that the syntax of a Transact-SQL statement is incorrect and that the syntax error was detected near the Transact-SQL syntax element specified in the error message. The most frequent causes for syntax errors are misspellings of Transact-SQL syntax elements or operators, and specifying the syntax of a Transact-SQL statement in the wrong order.

This example produces error 170:

USE Northwind
GO
SELECT &
FROM Categories
ORDER BY CategoryName ASC

Action

First, check the Transact-SQL statement syntax near the syntax element specified in the error message. Because Transact-SQL language syntax can be very complex, Microsoft SQL Server may report the syntax error later in the Transact-SQL statement syntax than it actually occurred. Second, reexamine the entire Transact-SQL statement that generated the error. Verify the syntax order of the statement.

In the above example, changing the ampersand (&) to an asterisk (*) corrects the problem:

USE Northwind
GO
SELECT *
FROM Categories
ORDER BY CategoryName ASC

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"Getting Started with Transact-SQL" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 207

Severity Level 16

Message Text

Invalid column name '%.*ls'.

Explanation

This error occurs when a column referenced in a Transact-SQL statement was not found in any table specified in the FROM clause of the query.

Action

Change the column name to a column name present in one of the tables referenced in the query.

This example returns error 207:

USE Northwind
GO
SELECT CategoryName1
FROM Categories
GO

Evaluate the problem by looking at the full command. For the above example, ensure that the SELECT statement contains the name of a valid column name (CategoryName rather than CategoryName1 as specified above) for the specified table.

USE Northwind
GO
SELECT CategoryName
FROM Categories
GO

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 208

Severity Level 16

Message Text

Invalid object name '%.*ls'.

Explanation

This error occurs when an object that does not exist is referenced. If the object exists, you might need to include the owner's name in the object name.

If the object is not owned by the user attempting to access it, and it is not owned by the database owner, all references to the object must include the owner's name. For example, if user1 creates a table called test, other users must use the name user1.test when they refer to the table.

The Microsoft SQL Server naming convention for database objects is:

[[[server_name.][database_name].][owner_name].]object_name

The default value for server_name is the current server and the default value for database_name is the current database. The default value for owner_name is the current user. Because owner_name is part of the object name, it is possible for two different users to have tables with the same name in the same database (for example, user1.test and user2.test). For additional information about naming conventions, see "Transact-SQL Syntax Conventions" in Microsoft SQL Server Transact-SQL and Utilities Reference.

This message can also occur when you reference a temporary table that was created with an EXECUTE statement.

Action

The procedure for handling this error depends on what you know about the object indicated in the error message text.

The appropriate permissions must also be set to allow access to an object. If these permissions are not set, error 229 or 230 occurs.

If you do not know who owns the object

Execute sp_help with no parameters to display the object owner.

Query the Information Schema Views if the object is a table or view to determine the object owner and type. If the object is not a table or a view, query the sysobjects system table to determine the object owner and type.

For example, to determine the owner and type for the object named table_1, execute the following:

USE master
GO
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'table_1'
GO

If no rows are returned from this query, the object either resides in a different database or does not exist.

If you do not own the object in question

Include the object owner in the object name. For example:
```
SELECT * 
```

FROM user1.table_1

Although using fully qualified object names eliminates this problem, remember that including the fully qualified object name in an application might complicate the maintenance of the application. For example, if all references to a table include the database name, changing the database name could become difficult.

Have the database owner create the object. If the owner creates the object, any user can find the object without specifying the owner. However, temporary tables reside in tempdb and are dropped automatically when the user process or server connection that created them is terminated. Users cannot share temporary tables, even if they are created by the database owner.

If the object does not reside in the database

Switch context to the correct database using the USE statement. For example:
```
USE database_1
```

Qualify the object name with the database name. For example:
```
SELECT * 
```

FROM database_1.user1.table_1

If you own the object or if the object is owned by the database owner, the owner name is not needed. For example: 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT *

FROM database_1..table_1

If a temporary table created with an EXECUTE statement is referenced

If you must use the EXECUTE statement to create a temporary table, create it as a global temporary table using the syntax ##tablename.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"CREATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Creating and Modifying a Table" in Microsoft SQL Server Database Developer's Companion

"EXECUTE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Information Schema Views" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Querying SQL Server System Catalogs" in Microsoft SQL Server Database Developer's Companion

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_help" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysobjects" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Transact-SQL Syntax Conventions" in Microsoft SQL Server Transact-SQL and Utilities Reference

"USE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 229

Severity Level 14

Message Text

%ls permission denied on object '%.*ls', database '%.*ls',
owner '%.*ls'.

Explanation

This error occurs when a Microsoft SQL Server user attempts an action, such as executing a stored procedure, or reading or modifying a table, for which the user does not have the appropriate privileges.

Action

Any user (the object owner or system administrator) with full-control over the object in question can grant the necessary privileges to the user requiring access to the object.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"EXECUTE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"GRANT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Managing Permissions" in Microsoft SQL Server Administrator's Companion

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 220

Severity Level 16

Message Text

Arithmetic overflow error for data type %ls, value = %ld.

Explanation

This error occurs when an attempt is made to convert a float or real data type value into a data type that cannot store the result. This error prevents the operation from being completed. For example, if you attempt to place the number 32770 into a variable or column of smallint data type, Microsoft SQL Server returns this error because variables or columns of smallint data type can address integers from 215 (–32,768) through 215 (32,767).

This example raises the error:

DECLARE @myval smallint
SET @myval = 32770
SELECT @myval
GO

Action

For numeric operations, use the ROUND, CAST, and CONVERT functions to manipulate the value in question to fit into the column or variable. Change the data type of the column or variable in question. In the example described above, change the column or variable from smallint to int).

Here is the corrected example:

DECLARE @myval int
SET @myval = 32770
SELECT @myval
GO

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"Data Types" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CAST and CONVERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"ROUND" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 245

Severity Level 16

Message Text

Syntax error converting the %ls value '%.*ls' to a column of
data type %ls.

Explanation

Microsoft SQL Server returns this message if a character is converted to an integer. For example, these SELECT statements return error 245:

SELECT CONVERT(int, 'A')
-- Or
SELECT CAST('A' AS int)

SQL Server returns this error message because a conversion from a character value to an integer can only be done if it resembles a numeric value. For example, the character 1 (one) can be converted to an integer.

SELECT CONVERT(int, '1')
-- Or
SELECT CAST('1' AS int)

Action

To convert a character to an integer, use the ASCII function, which returns a numerical representation of the character. For example:

SELECT CONVERT(int, ASCII('A'))
-- Or
SELECT CAST(ASCII('A') AS int)

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"ASCII" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CAST and CONVERT" in Microsoft SQL Server Building Applications

"Data Types" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 259

Severity Level 16

Message Text

Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this.

Explanation

This error occurs when Microsoft SQL Server detected an attempt to modify the system catalogs directly while the allow updates system configuration option of sp_configure is set to 0.

The allow updates system configuration option allows the system administrator to change the system catalogs directly.

Warning Severe problems can result from the direct manipulation of the system catalogs. Do not modify the system catalogs unless instructed to do so by your primary support provider.

Action

Before modifying any system catalogs, be sure that you have a valid backup of the database. For more information about backup operations, see "Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion.

Warning Incorrect modification of the system catalogs can result in database corruption or data loss.

If possible, restart SQL Server in single-user mode by using the -m flag of the sqlservr application so that inadvertent modifications do not occur. For more information, see "sqlservr Application" in Microsoft SQL Server Transact-SQL and Utilities Reference.

To modify system catalogs, use the osql utility to alter the allow updates system configuration setting.

Note Only the system administrator can alter the value for the allow updates system configuration setting.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"allow updates Option" in Microsoft SQL Server Administrator's Companion

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"osql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"RECONFIGURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SQL Server Startup Options" in Microsoft SQL Server Administrator's Companion

Error 266

Severity Level 16

Message Text

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = %ld, current count =
%ld.

Explanation

If a stored procedure exits with the @@TRANCOUNT value that is not the same as when the stored procedure was entered, Microsoft SQL Server returns error 266.

Note This error can be ignored because it only sends a message to the client and does not affect execution.

This example reproduces the problem:

CREATE PROCEDURE test
AS
SELECT @@TRANCOUNT
ROLLBACK TRANSACTION
SELECT @@TRANCOUNT
GO
BEGIN TRANSACTION
EXECUTE test
GO

Because @@TRANCOUNT is not the same in both SELECT statements, error 266 is generated on return from the stored procedure.

This is expected behavior, but it does not mean that transactions cannot be started, completed, or terminated in a stored procedure. Instead, care must be taken so that the @@TRANSACTION function matches on both entry and exit of the stored procedure. For more information, see "ROLLBACK TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference.

This problem is more likely to occur when writing nested stored procedures.

Action

There is solution so that the stored procedure works without the error. The following is a list of solutions, with sample code for each:

Perform final COMMIT TRANSACTION or ROLLBACK TRANSACTION statements from the same stored procedure nesting level where the transaction began, as shown by the following examples:
```
-- Example 1.a
```

CREATE PROCEDURE test1a AS SELECT @@TRANCOUNT GO BEGIN TRANSACTION EXECUTE test1a ROLLBACK TRANSACTION GO -- Example 1.b CREATE PROCEDURE test1c AS SELECT @@TRANCOUNT GO CREATE PROCEDURE test1b AS BEGIN TRANSACTION EXEC test1c COMMIT TRANSACTION GO EXECUTE test1b GO

If nested transactions are used in a stored procedure, perform matching commits.

Note The transaction is not committed until @@TRANCOUNT is equal to 0 (zero).
```
-- Example 2
```

CREATE PROCEDURE test2b AS SELECT @@TRANCOUNT BEGIN TRANSACTION SELECT @@TRANCOUNT COMMIT TRANSACTION SELECT @@TRANCOUNT GO CREATE PROCEDURE test2a AS BEGIN TRANSACTION EXECUTE test2b COMMIT TRANSACTION GO EXECUTE test2a GO

If a rollback is needed and the stored procedure nesting level is different than where the transaction began, use RAISERROR, with a valid user-defined error, and check the @@ERROR function after the EXECUTE statement.
```
-- Example 3
```

USE master EXECUTE sp_addmessage 50001, 16, 'Rollback of transaction in test3' GO CREATE PROCEDURE test3 AS RAISERROR (50001,16,1) GO BEGIN TRANSACTION EXEC test3 IF @@error <> 50001 BEGIN PRINT 'Commit' COMMIT TRANSACTION END ELSE BEGIN PRINT 'Rollback' ROLLBACK TRANSACTION END GO

The exception to this rule is that if a trigger performs a rollback, @@TRANCOUNT need not match its starting value, because the batch is terminated. However, a stored procedure called by a trigger may cause the problem if it terminated the transaction.
```
-- Example 4
```

CREATE TABLE x (col1 int) GO CREATE TRIGGER xins ON x FOR INSERT AS ROLLBACK TRANSACTION GO CREATE PROCEDURE sp_xinsert AS SELECT @@TRANCOUNT INSERT x (col1) VALUES (1) SELECT @@TRANCOUNT GO BEGIN TRANSACTION EXECUTE sp_xinsert IF @@error <> 0 BEGIN PRINT 'Commit' COMMIT TRANSACTION END ELSE BEGIN PRINT 'Rollback' ROLLBACK TRANSACTION END GO SELECT * FROM x

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"@@ERROR" in Microsoft SQL Server Transact-SQL and Utilities Reference

"@@TRANCOUNT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BEGIN TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference

"COMMIT TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference

"EXECUTE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"ROLLBACK TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Transactions" in Microsoft SQL Server Database Developer's Companion

Error 268

Severity Level 16

Message Text

Cannot run SELECT INTO in this database. The database owner
must run sp_dboption to enable this option.

Explanation

This error occurs when an attempt to use the SELECT INTO statement has not been permitted because the select into/bulkcopy database option is not enabled for this database. The database owner must turn on the select into/bulkcopy database option before the SELECT INTO statement can be completed successfully.

This error can also be triggered by stored procedures.

Caution Enabling the select into/bulkcopy database option permits nonlogged operations to take place. Have a specific backup strategy in place to ensure data integrity after nonlogged operations have been performed. A transaction log cannot be backed up after a nonlogged operation. Use the BACKUP DATABASE statement after nonlogged operations have been performed.

Action

The database owner or system administrator must use the sp_dboption system stored procedure to enable the select into/bulkcopy database option. For example, for the pubs database, you would use the osql command prompt utility to perform the following steps:

Switch to the master database and set the database option for the Northwind database. Type:
```
USE master
```

GO sp_dboption Northwind, 'select into/bulkcopy', true GO USE Northwind GO CHECKPOINT GO

Verify that the change has taken place correctly. Execute the sp_helpdb system stored procedure on Northwind. The status result column should show the select into/bulkcopy database option as enabled. Type:
```
sp_helpdb Northwind
```

You may want to review information about the bcp Utility and SELECT INTO statement. For more information, see "SELECT" and "bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference. In some cases, bcp is a logged operation that can affect your backup strategy and transaction log backup frequency.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"osql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_help" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_helpdb" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 511

Severity Level 16

Message Text

Cannot create a row of size %d which is greater than the allowable
maximum of %d.

Explanation

This error occurs when you attempt to insert a row that is larger than the maximum defined for that table. This error occurs if the row you attempt to insert into a table is too big to fit into a data page.

In Microsoft SQL Server, the maximum allowable size of a row in a table is 8060 bytes. A row cannot be split across data pages. A data page is 8 KB in size and consists of the data row and some internal data structures.

Action

Change the data being inserted so it does not exceed the maximum number of bytes (8060) that can be stored in a single row.

See Also

In Other Volumes

"Adding Rows with INSERT" in Microsoft SQL Server Database Developer's Companion

"CREATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Maximum Capacity Specifications" in Microsoft SQL Server Introduction

Error 515

Severity Level 16

Message Text

Cannot insert the value NULL into column '%.*ls', table '%.*ls';
column does not allow nulls. %ls fails.

Explanation

This error occurs at run time when an attempt is made to use a null value while inserting or updating a column that does not allow null values.

Note This message differs from the following message, which indicates that the attempt has been detected at compile time.

error 233: The column '%.*ls' in table '%.*ls' cannot be null.

This error can also occur if the table that is the target of an INSERT or UPDATE statement in a stored procedure or trigger is being dropped and re-created, and one or more of the table column definition(s) have changed from NULL to NOT NULL.

Action

If this error occurs when you are running an UPDATE or INSERT statement, verify that the data inserted or updated matches the column definition for the affected table.

Inserting or updating a column does not allow null values.

Note In a direct UPDATE or INSERT by value, you will get compile error 233 instead of error 515. Usually 515 errors occur in an INSERT/SELECT or an UPDATE statement that uses data in another table.

If this error occurs when a stored procedure or trigger references a table that has been dropped and re-created with different nullability, drop and re-create the affected stored procedure or trigger.

If you are unable to resolve the problem, contact your primary support provider for assistance.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"ALTER PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"ALTER TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference

"ALTER TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CREATE PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CREATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CREATE TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DROP PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DROP TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 544

Severity Level 16

Message Text

Cannot insert explicit value for identity column in table '%.*ls'
when IDENTITY_INSERT is set to OFF.

Explanation

This error occurs when you have attempted to insert a row that contains a specific identity value into a table that contains an identity column. However, SET IDENTITY_INSERT is not enabled for the specified table.

Action

To insert a specific identity row successfully into a table containing an identity column, you must enable SET IDENTITY_INSERT. The following example inserts identity row 2, where iID * *is defined as the identity column.

USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'tblTest')
DROP TABLE tblTest
GO
CREATE TABLE tblTest
( iID int IDENTITY(1, 1), 
strData nvarchar(15)
)
GO
INSERT INTO tblTest (strData) VALUES (N'Leverling')
INSERT INTO tblTest (strData) VALUES (N'Davolio')
GO
SET IDENTITY_INSERT tblTest ON
GO

-- Insert the specified identity row using a column list.
INSERT INTO tblTest (iID, strData) VALUES (5, N'Callahan')
GO
-- Display the rows in tblTest to see identity values.

SELECT *
FROM tblTest

-- Disable IDENTITY_INSERT.
SET IDENTITY_INSERT tblTest OFF
GO

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"ALTER TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CREATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SET IDENTITY_INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 601

Severity Level 12

Message Text

Could not continue scan with NOLOCK due to data movement.

Explanation

When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft SQL Server is not able to continue the scan.

Action

This error aborts the query. Either resubmit the query or remove the NOLOCK locking hint.

See Also

In Other Volumes

"Locking Hints" in Microsoft SQL Server Database Developer's Companion

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SET TRANSACTION ISOLATION LEVEL" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 602

Severity Level 21

Message Text

Could not find row in sysindexes for database ID %d, object ID %ld,
index ID %d. Run DBCC CHECKTABLE on sysindexes.

Explanation

This error occurs when Microsoft SQL Server cannot find a row in the sysindexes table for a needed table or index (for example, when executing a stored procedure that references a table that has been dropped).

Action

If the error occurs because a stored procedure references a dropped table, drop and re-create the stored procedure. This error may also occur in conjunction with other error messages that better point to the root cause of the problem. Execute DBCC CHECKTABLE on sysindexes; also execute DBCC CHECKDB.

If the problem persists, contact your primary support provider. Have the output from the DBCC CHECKDB statement available for review.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"ALTER PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CREATE PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKTABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DROP PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysindexes" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 605

Severity Level 21

Message Text

Attempt to fetch logical page %S_PGID in database '%.*ls' belongs to
object '%.*ls', not to object '%.*ls'.

Explanation

This fatal error occurs when Microsoft SQL Server detects database corruption. The second object specified in the text not to object '%.*ls' is probably corrupt. Because this error can mask the existence of other errors, execute DBCC CHECKDB to determine the extent of the damage. If DBCC CHECKDB does not report additional errors, the first object mentioned is not corrupt.

SQL Server detects database corruption when it traverses the pages of an object and finds a page in the chain whose object ID does not match that of the object being accessed. There is probably either a damaged page chain, a corrupt Index Allocation Map (IAM), or an invalid entry in the sysobjects system table for that object. A clustered table has one doubly-linked page chain for the table data as well as one for each index level. A nonclustered index has a page chain for each level of the index. Pages in a heap are not linked. The IAM is used to find the pages of a heap.

Although error 605 usually displays two object names, other variations can occur:

If instead of an object name the error displays a number greater than 0, it means that an attempt was made to reference an object ID that does not exist in a system table for that object.
If the error reports the first object ID as 0, an unallocated page was probably encountered. (There is no object ID equal to 0.)
If the error states that a page belongs to object ALLOCATION, some of the allocation structures used by the database might be corrupted.

Usually this error occurs after the corruption has been written to the database on disk, but it can also occur entirely in cache without the damage ever being written to disk. This is known as a transient 605 error and is not associated with data corruption. If error 605 occurs during data access, but subsequent DBCC CHECKDB statements complete without error, the 605 error was probably transient. Transient 605 errors can be caused by the operating system prematurely notifying SQL Server that an I/O operation has completed; the error message is displayed even though no actual data corruption exists.

Nontransient 605 errors are often caused by hardware or disk device driver failure.

Action

Execute DBCC CHECKTABLE on the second object specified in the error message. To determine the full extent of the corruption, execute DBCC CHECKDB as soon as possible. Also check the error log for other errors, which often accompany a 605 error.

If the 605 error is not transient, the problem is severe and you must run DBCC CHECKDB with one of the repair clauses. If the error involves an index page, use the REPAIR_REBUILD clause. If the error involves a data page, it may be necessary to use the REPAIR_ALLOW_DATA_LOSS clause. In the likely event that you cannot allow the loss of data, you will need to restore from a known clean backup. If the problem persists, contact your primary support provider. Have the output from DBCC CHECKDB available for review.

Important If executing DBCC CHECKDB with one of the repair clauses does not correct the index problem or if you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact your primary support provider.

In addition, run hardware diagnostics and correct any problems. You might find it beneficial to perform a completely new setup on the computer, including reformatting the disk drives and reinstalling the operating system. This eliminates the possibility that a .Dll or .Exe program is corrupted. You can also examine your operating-system error log to see if the error occurred as the result of hardware failure.

Finally, be sure that your system does not have write caching enabled on the disk controller. If you suspect this to be the problem, contact your hardware vendor.

Additional Information

DBCC CHECKDB offers the REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS clauses. The REPAIR_REBUILD clause rebuilds corrupt indexes and the REPAIR_ALLOW_DATA_LOSS clause fixes allocation problems. Sometimes, deleting pages is the only way to fix allocation problems. Typically, these pages contain data that was already deleted, but the pages may contain valid data. Therefore, deleting pages is a more risky option than using DBCC CHECKDB with a repair clause. Using DBCC CHECKDB with a repair clause fixes database corruption when a database backup is not available.

If your database is a data warehouse, you may be able to continue operating without the lost data for some time before reloading the missing data. In these cases, use DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause to fix the damaged database.

You can prevent problems by following these guidelines:

Run SQL Server only on hardware and controllers that are certified for your operating system.
Perform regular backups in conjunction with DBCC CHECKDB statements. DBCC CHECKDB performs all checks that DBCC NEWALLOC and DBCC CHECKALLOC previously did and DBCC CHECKDB is faster. This is the only way to be confident of the state of the database at the time of the backup.
If the data is critical, back up the transaction log frequently. This makes it possible to reduce your window of vulnerability, even in the event of a catastrophic hardware problem, to an hour or less.
In the most critical situations, use a standby server and a continually running batch job to take transaction backups off of the primary computer and continually restore them on the standby computer.
If you have persistent data corruption problems, try to swap the computer, the controllers, and the disk device drivers for components of a different type. This makes it easier to determine whether the problem is platform-specific.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"BACKUP" in Microsoft SQL Server Building Applications

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion"

"Managing Space Used by Objects" in Microsoft SQL Server Introduction

"Using Standby Servers" in Microsoft SQL Server Administrator's Companion

Error 624

Severity Level 21

Message Text

Could not retrieve row from page by RID because the requested RID has a
higher number than the last RID on the page. %S_RID.%S_PAGE, Dbid %d

Explanation

This error occurs when an attempt to retrieve a row from a data page by specifying the row ID (RID) failed because the requested RID was a higher number than the last RID on the page. This can happen during normal processing, if the leaf page of a corrupt nonclustered index points to an incorrect or nonexistent RID on a data page.

If the error occurs on a system table during a read-only operation while other users are updating system tables (executing DDL), it is probably a transient 624 error (rather than a corrupted index). To confirm that there is no corruption, execute DBCC CHECKTABLE without a repair clause.

Action

The recovery procedure depends on when the error occurred. If problems persist, the following procedures might not be sufficient to clean up the index corruption. In this case, contact your primary support provider. Have the output from either DBCC CHECKTABLE (if investigating possible system table corruption) or DBCC CHECKDB available for review.

If the error occurred during normal processing

Execute DBCC CHECKTABLE with the REPAIR_REBUILD clause. If executing DBCC CHECKTABLE with the REPAIR_REBUILD clause does not correct the problem, drop and re-create the affected index or indexes.

Important If executing DBCC CHECKDB with the REPAIR_REBUILD clause does not correct the index problem or if you are unsure what effect DBCC CHECKDB with the REPAIR_REBUILD clause has on your data, contact your primary support provider.

Index unknown, query known

The fastest way to resolve this problem is to execute DBCC CHECKDB with the REPAIR_REBUILD clause. This fixes any index corruption in the entire database. If the database is so large that you do not want to run DBCC CHECKDB, use these instructions to locate the specific index to drop and re-create.

If you do not know which index is causing the problem but you do know which query encounters the problem, follow the instructions below. If you do not know the index or the query, follow the instructions under the next section, "Index and query both unknown."

Determine which index should be dropped by reading the showplan output for the query that encounters the error. If you SET SHOWPLAN_TEXT to ON, SET NOEXEC to ON, and then run the query in question, the output indicates which nonclustered index the query is using to access the table in question.

For example:
```
USE pubs
```

GO SET SHOWPLAN_TEXT ON GO SET NOEXEC ON GO SELECT title FROM titles WHERE title > 'Cooking' GO

Here is the result set:

StmtText 
---------------- 
SET NOEXEC ON

(1 row(s) affected)

StmtText 
------------------------------------------------------- 
SELECT title 
FROM titles 
WHERE title > 'Cooking'

(1 row(s) affected)

StmtText 
--------------------------------------------------------------------- 
|--Index Seek(pubs..titles.titleind, SEEK:(titles.title > @1) ORDERED)
(1 row(s) affected)

SET NOEXEC to OFF and SET SHOWPLAN_TEXT TO OFF again:
```
SET NOEXEC OFF
```

GO SET SHOWPLAN_TEXT OFF GO

Drop and re-create the index identified in Step 1 (in this example, titleind).

Index and query both unknown

If you do not know the index or the query, rebuild all nonclustered indexes on the table as follows:

After the appropriate index has been re-created, run DBCC CHECKTABLE on the table to confirm that the problem has been resolved.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

Resetting the Suspect Status

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKTABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SET NOEXEC" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SET SHOWPLAN_TEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_helpindex" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 625

Severity Level 21

Message Text

Could not retrieve row from logical page %S_PGID by RID because the
entry in the offset table (%d) for that RID (%d) is less than or equal
to 0.

Explanation

This error occurs when an object to which this row belongs is corrupt. The corruption was detected during the reading of a specific row from that logical page. This corruption can occur as a result of:

Hardware problems, especially problems with the hard drive, controller or hardware write caching.
Other corruption in the database.

Action

Execute DBCC CHECKDB to determine the full extent of the corruption. Also check the error log for other errors such as 25xx messages, which often accompany this error.

If DBCC CHECKDB issues other corruption messages, resolve those messages first; that may take care of this error. Execute DBCC CHECKDB with the REPAIR_REBUILD clause to repair the damage.

If executing DBCC CHECKDB with the REPAIR_REBUILD clause does not resolve all corruption problems, determine which table is corrupt by examining the page in the error message. If the page is associated with an index, you may be able to resolve the problem by dropping and then re-creating the index. If the page is a data page and a clean current backup is available, restore the database from the backup.

If no backup is available, select the table into another table or bulk copy data out of the table, drop the table, re-create it, and then select data back in to the table.

If you suspect a hardware problem, run hardware diagnostics and correct any problems. You might find it beneficial to perform a completely new setup, including reformatting the disk drives and reinstalling the operating system. This eliminates the possibility that a .DLL or .EXE program is corrupted. Also examine the Microsoft Windows NT application log to see if the error occurred as the result of hardware failure.

Finally, be sure that your system does not have write caching enabled on the disk controller. If you suspect this to be the problem, contact your hardware vendor.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 644

Severity Level 21

Message Text

Could not find the index entry for RID '%.*hs' in index page %S_PGID,
index ID %d, database '%.*ls'.

Explanation

This error occurs when the nonclustered index indicated by the index ID is corrupt. The corruption is detected when a process tries to delete a nonexistent row.

Action

Execute DBCC CHECKDB without a repair clause to determine the extent of the damage. Then, execute DBCC CHECKDB with REPAIR_REBUILD clause to correct the damage. If problems persist, either drop and re-create the index (as shown below) or contact your primary support provider.

To drop and re-create the index:

Record the value of index page and index ID specified in the error text.
Identify which table and index correspond to the index page number.
Note the object ID.

If the object with the error is a system table (the object ID is less than 100), you cannot drop the index. Execute DBCC CHECKDB with the REPAIR_REBUILD clause or restore the database from a known clean backup.
If the object ID is greater than 100, drop and re-create the index using the table name and index name obtained in Step 1. In most cases, this will clear the corruption.
Execute DBCC CHECKDB with the REPAIR_REBUILD clause on the affected database to verify that all problems have been resolved.

If problems persist, this procedure might not be sufficient to clean up the index corruption. In this case, contact your primary support provider. Have the output from DBCC CHECKDB available for review.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DROP INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 701

Severity Level 19

Message Text

There is insufficient system memory to run this query.

Explanation

The memory requirements for the set of tasks Microsoft SQL Server is attempting to perform exceeds the amount of available memory. Either increase the amount of server memory or reduce the server workload.

Action

To decrease the server workload, reduce the number of users currently using SQL Server. To prevent additional users from logging in to SQL Server, pause the server. For more information, see "Pausing and Resuming SQL Server" in Microsoft SQL Server Administrator's Companion.

To increase server memory:

Check the settings for both min server memory (MB) and max server memory (MB).

If max server memory (MB) is a value close to the value of min server memory (MB), then increase the max server memory (MB) value.
Check the size of the virtual memory paging file.

If possible, increase the size of the file.
Shut down any other applications running, if applicable, on the server.
View the current memory usage information in Windows NT Performance Monitor.

To view current memory usage information in preparation for tuning memory configuration, use the Total Server Memory (KB) Performance Monitor Counter of the SQLServer:General Statistics object.
Add additional memory to the server.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"Programming Stored Procedures" in Microsoft SQL Server Database Developer's Companion

"Server Memory Options" in Microsoft SQL Server Administrator's Companion

"SQL Server: General Statistics Object" in Microsoft SQL Server Administrator's Companion

Error 813

Severity Level 20

Message Text

Logical page %S_PGID in database ID %d is already hashed.

Explanation

This error occurs when Microsoft SQL Server attempts to hash logical page %S_PGID of database ID%d and the page is already in the SQL Server hash table.

This error usually occurs as a side effect of other data corruption that can usually be detected by executing DBCC CHECKDB without a repair clause.

Action

Review the SQL Server error log, and then execute DBCC CHECKDB with the appropriate repair clause on the database. If the error involves an index page, use the REPAIR_REBUILD clause. If the error involves a data page, it may be necessary to use the REPAIR_ALLOW_DATA_LOSS clause. In the likely event that you cannot allow the loss of data, you will need to restore from a known clean backup. If the problem persists, contact your primary support provider. Have the output from DBCC CHECKDB available for review.

Verify with your hardware vendor that the disk subsystem being used by SQL Server is at the currently supported and recommended firmware and hardware level.

If DBCC CHECKDB finds no errors, or if errors are found and corrected so that subsequent DBCC statements indicate no errors, but this error reoccurs, contact your primary support provider. Have the SQL Server error logs and the output from DBCC CHECKDB and sp_configure, available for review.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKALLOC" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 822

Severity Level 21

Message Text

Could not start I/O for request %S_BLKIOPTR.

Explanation

Microsoft SQL Server encountered an error while attempting to initiate a read or write request. This error can occur for any of the following reasons:

A database file is off-line.
A database file has been removed or renamed.
A database file is inaccessible for another reason (such as insufficient permissions).

The parameter in the error message refers to an internal structure and does not help determine which database file is involved. However, other error messages appearing in the error log just before error 822 usually indicate the file involved.

Action

Examine the availability and condition of the file involved using your standard operating-system procedures, and be sure the file is accessible.

Because error 822 marks the database as suspect, SQL Server cannot recover the database upon restarting. If you know that the database was marked suspect because the file was unavailable, you can reset the status of the database to allow recovery to continue.

Caution Do not use these procedures if there are other errors in the error log, near the error 822 message, that suggest that the database might have been marked suspect for some other reason.

Reset the suspect status by executing sp_resetstatus. This is the safest method. After you execute sp_resetstatus, restart SQL Server.

Here are two examples of error 822 and the associated messages from the log. Examining the error messages that occurred just before error 822 usually helps determine the type of problem.

Device missing

In this example, the device C:\Mssql7\data\mydb_data.mdf did not exist when SQL Server started, causing access to the device to fail:

kernel udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\MSSQL7\data\mydb_Data.MDF.
kernel FCB::Open failed: Could not open device C:\MSSQL7\data\mydb_Data.MDF for virtual device number (VDN) 1.
spid9 Device activation error. The physical file name 'C:\MSSQL7\data\mydb_Data.MDF' may be incorrect.

To correct an error like this, be sure that the virtual device appearing in the error message exists, and correct whatever error prevented SQL Server from finding it. For example, it might have been renamed or moved while SQL Server was not running.

If the virtual device no longer exists, you must restore from known clean backups. Merely creating an empty device with the correct name will not solve this problem.

Permission problems

Permission problems can occur if the device is on an NTFS partition.

In this example, the permissions on C:\Mssql7\Data\ Mydb_DATA.Mdf were incorrect, so SQL Server could not access it:

kernel udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\MSSQL7\data\mydb_Data.MDF.
kernel: dopen: open "c:\mssql7\data\mydb_data.mdf", Permission denied
kernel FCB::Open failed: Could not open device C:\MSSQL7\data\mydb_Data.MDF for virtual device number (VDN) 1.
spid9 Device activation error. The physical file name 'C:\MSSQL7\data\mydb_Data.MDF' may be incorrect.

To correct an error like this, change the object ownership for the device to Administrator, and be sure that the account that SQL Server was started under has read and write permissions on the object.

See Also

In This Volume

Errors 1 - 999

Resetting the Suspect Status

In Other Volumes

"BEGIN TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CHECKPOINT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"RECONFIGURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion

"SHUTDOWN" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 823

Severity Level 24

Message Text

I/O error %ls detected during %S_MSG of %S_BUF.

Explanation

Microsoft SQL Server encountered an I/O error on a read or write request made to a device. This error usually indicates disk problems. The parameters in the error message refer to internal structures and don't often help determine which device is involved. However, additional kernel messages in the error log, recorded before error 823, should indicate which device is involved.

Action

Check the accessibility and condition of the device in question. After you have identified and corrected the problem, execute DBCC CHECKDB to ensure that no other damage was caused by the bad device. For information about using DBCC CHECKDB, see "DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference.

In addition, run hardware diagnostics and correct any problems.

See Also

In This Volume

Errors 1 - 999

Error 844

Severity Level 10

Message Text

Time out occurred while waiting for buffer latch type %d, bp %#x, page
%S_PGID, stat %#x, object ID %d:%d:%d, waittime %d. Continuing to wait.

Explanation

When under a heavy stress load or high I/O conditions, your system may produce this message.

Action

This message can usually be ignored; however, if you receive repeated messages where the wait time increases, it may indicate an internal server problem. Contact your system administrator. The system administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of sysprocesses to see what activities each SPID is performing.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"sysprocesses" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 845

Severity Level 17

Message Text

Time out occurred while waiting for buffer latch type %d for page
%S_PGID, database ID %d, object ID %d, index ID %d.

Explanation

When under a heavy stress load or high I/O conditions, your system may produce this message.

Action

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"sysprocesses" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 911

Severity Level 16

Message Text

Could not locate entry in sysdatabases for database '%.*ls'. No entry
found with that name. Make sure that the name is entered correctly.

Explanation

This error occurs when attempting to change database context (with a USE statement) to a database that does not exist or when the default database established for a login does not exist. In the latter case, the user login then attempts to access the master database.

Action

To obtain a list of databases, execute sp_helpdb or issuing this query:

SELECT name 
FROM master..sysdatabases

The list returned will contain the databases that exist on the Microsoft SQL Server installation. Either create a missing database or connect to an existing one. To correct login-level errors, it may be necessary to execute sp_defaultdb.

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"sp_defaultdb" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_helpdb" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysdatabases" in Microsoft SQL Server Transact-SQL and Utilities Reference

"USE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 913

Severity Level 22

Message Text

Could not find database ID %d. Database may not be activated yet or
may be in transition.

Explanation

This error can occur if there is a problem with the view resolution process. During execution of various compiled objects in a database that reference the database dbid (such as stored procedures and views), it is a normal process to resolve
the dbid with other structures within the database. When a compiled object is first created, the dbid where the object is located is embedded in the compiled code. For example, when a view is accessed or a stored procedure is executed, the rights to access the view or execute the stored procedure are checked.

Action

If the error occurs when a stored procedure or view is accessed, you may be able to correct the problem by simply dropping and re-creating the database object (stored procedure or view).

If this error continues to occur, contact your primary support provider and have the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred available for review.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"CREATE PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CREATE VIEW" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DROP PROCEDURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DROP VIEW" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 924

Severity Level 14

Message Text

Database '%.*ls' is already open and can only have one user at a time.

Explanation

This error occurs when trying to access a database that is already in use by another user or session. Microsoft SQL Server detected an attempt to access a database that is in single-user mode.

Action

Verify that the database in question is actually in single-user mode by executing this query (substitute your database name for <database>).

sp_helpdb <database>
GO

If the database is truly in single-user mode, the status result set column heading will list single user as the access mode.

To see what login is accessing the database, execute sp_who and scan the dbname result set column heading for the specified database.

If single-user mode privilege is a problem, contact your system administrator and ask to have the single-user mode database option set to multiuser. The system administrator can do so by executing sp_dboption from the master database, as shown here:

sp_dboption database, 'single user', false

See Also

In This Volume

Errors 1 - 999

In Other Volumes

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_helpdb" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_who" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 926

Severity Level 14

Message Text

Database '%.*ls' cannot be opened. It has been marked SUSPECT by
recovery. See the SQL Server errorlog for more information.

Explanation

This informational message occurs when an attempt to open or use a database that has a sysdatabases.status column value greater than or equal to 256 and less than 1024.

The database is marked as suspect because it failed the recovery process that brings a database to a consistent transactional state during startup or during the RESTORE DATABASE or RESTORE LOG procedures.

Action

Inspect the Microsoft SQL Server error log and determine the cause of the error. If SQL Server has been restarted since the failed recovery, look at
previous SQL Server error logs to see the reason why recovery failed. Correct the underlying problem. If you are unable to resolve this problem, contact your primary support provider. Have the SQL Server error log available for review.

See Also

In This Volume

Errors 1 - 999

Reporting Errors to Your Primary Support Provider

Resetting the Suspect Status

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysdatabases" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 945

Severity Level 16

Message Text

Database '%.*ls' cannot be opened because some of the files could
not be activated.

Explanation

Microsoft SQL Server returns error 945 and does not set the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database due to any situation other than the disk being full.

Action

To determine the cause of, and possible solution for the problem, use the appropriate DATABASEPROPERTY property. For example, to determine if the database is offline, use the IsOffline property of DATABASEPROPERTY.

Enabled DATABASEPROPERTY	Error message resolution
IsShutDown**	Fixing the database and log files and restarting the server.
IsEmergencyMode	Explicitly changing the sysdatabases.status bit.
IsInLoad*	Retrying the restore operation, continuing the restore operation if it occupied multiple tapes, or dropping the database and trying again.
IsInRecovery*	Doing nothing, except allowing recovery to continue.
IsInStandby*	Doing nothing. This server is acting as a warm backup or standby server. For more information, see "Using Standby Servers" in Microsoft SQL Server Administrator's Companion.
IsOffline**	Executing sp_dboption with a value of false for the offline setting.
IsSuspect	Freeing disk space if the operating system ran out of disk space during recovery. Reset the sysdatabases.status bit, and then restart the server. Contact your primary support provider if none of these options is successful.

* Indicates that these options can be enabled, in any combination, simultaneously.
** Indicates that neither IsOffline nor IsShutDown should be enabled when IsSuspect is enabled.

See Also

In Other Volumes

"DATABASEPROPERTY" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1002

Severity Level 16

Message Text

Line %d: Specified scale %d is invalid.

Explanation

This error occurs when the scale of a decimal or numeric column does not fall between the allowable minimum and maximum values based on the precision of the column.

Action

Specify the precision with p, which must be between 1 and the maximum precision, and the scale with s, which must be between 0 and p. If you do not specify the precision, Microsoft SQL Server uses a default precision of 18. If you do not specify the scale, SQL Server uses a default scale of 0. For more information, see "decimal and numeric" and "Precision, Scale, and Length" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Error 1105

Severity Level 17

Message Text

Could not allocate space for object '%.*ls' in database '%.*ls' because
the '%.*ls' filegroup is full.

Explanation

The specified filegroup has run out of free space.

Action

You can:

Free disk space on any disk containing a file in the filegroup mentioned in the 1105 error message. Freeing disk space allows the files in the filegroup to grow.

Free disk space on a different disk drive.
Move the data files in the filegroup with an insufficient amount of free disk space to the disk drive in Step 1.
Detach the database by executing sp_detach_db.
Attach the database by executing sp_attach_db, pointing to the moved files.

Either add a data file to the specified database by using the ADD FILE clause of the ALTER DATABASE statement, or enlarge the data file by using the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax.

See Also

In This Volume

Errors 1000 - 1999

Insufficient Disk Space

In Other Volumes

"ALTER DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Expanding the Database" in Microsoft SQL Server Database Developer's Companion

"sp_attach_db" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_detach_db" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1203

Severity Level 20

Message Text

Process ID %d attempting to unlock unowned resource %.*ls.

Explanation

This error occurs when Microsoft SQL Server is engaged in some activity other than normal post-processing cleanup and it finds that a particular page it is attempting to unlock is already unlocked. The underlying cause for this error may be related to structural problems within the affected database. SQL Server manages the acquisition and release of pages to maintain concurrency control in the multiuser environment. This mechanism is maintained through the use of various internal lock structures that identify the page and the type of lock present. Locks are acquired for processing of affected pages and released when the processing is completed.

Action

Execute DBCC CHECKDB against the database in which the object belongs. If DBCC CHECKDB reports no errors, attempt to reestablish the connection and execute the command.

See Also

In This Volume

Errors 1000 - 1999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1204

Severity Level 19

Message Text

SQL Server has run out of LOCKS. Rerun your statement when there
are fewer active users, or ask the system administrator to
reconfigure SQL Server with more LOCKS.

Explanation

This error occurs when there are not enough system locks to complete the current command.

Action

Either execute the command again, when activity on the server is low, or have the system administrator increase the number of locks by executing sp_configure from the master database.

To view the current configuration:

sp_configure locks
GO

This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:

sp_configure locks, 10000
GO
RECONFIGURE WITH OVERRIDE
GO

Stop and restart Microsoft SQL Server so the changes can take effect. Locks are allocated at system startup.

If the number of locks cannot be increased at the current time, and the single action requires more locks than the server is currently configured for, you may be able to reduce the number of locks required for the operation. For example, try the following:

For large UPDATE statements, break the updates into smaller units that will affect only a subset of records at a time. For example, you could use the primary key, changing the single UPDATE statement from:
```
UPDATE employees
```

SET salary = salary * 1.05 WHERE employee_id BETWEEN 1000 AND 9999 GO

to several UPDATE statements: 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">UPDATE employees

SET salary = salary * 1.05 WHERE employee_id BETWEEN 1000 AND 4999 GO UPDATE employees SET salary = salary * 1.05 WHERE employee_id BETWEEN 5000 AND 9999 GO

For a maintenance type of task or for a global update, consider putting the database into single-user mode (if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).
For a large bcp operation, the entire operation is treated as a single transaction. When you use the batch parameter (-b), bcp will treat the operation in small transactions with the number of rows specified. At the end of each small transaction, the system resources held by that transaction are freed, so fewer locks are needed.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

"Starting, Pausing, and Stopping SQL Server" in Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1205

Severity Level 13

Message Text

Your transaction (process ID #%d) was deadlocked with another process
and has been chosen as the deadlock victim. Rerun your transaction.

Explanation

This error occurs when Microsoft SQL Server encounters a deadlock. A deadlock occurs when two (or more) processes attempt to access a resource that the other process holds a lock on. Because each process has a request for another resource, neither process can be completed. When a deadlock is detected, SQL Server rolls back the command that has the least processing time and returns this error message 1205 to the client application. This error is not "fatal" and may not cause the batch to be terminated.

Action

In some instances, a deadlock condition will cause a DB-Library function (such as dbsqlexec, dbsqlok, dbresults, or dbnextrow) to return FAIL. It is always the responsibility of the program to check the return codes from each DB-Library function. If FAIL is returned by one of these DB-Library functions, the program should cancel the batch and not attempt to continue. In some cases, it is possible to continue execution of subsequent functions in the batch. However, because a deadlock situation occurred and the functions that caused it were rolled back, later functions in the batch will probably fail with a more serious error, such as "object not found."

In other instances, a deadlock condition will not cause a DB-Library function to return FAIL. In these cases, the program must check for error message 1205 in the message handler and use the dbsetuserdata function to communicate this to the application (For more information, see "dbsetuserdata" in SQL Server Books Online.) The program must then check for the deadlock indicator after every DB-Library call and should cancel the batch if a deadlock is detected.

While it may seem unnecessary to cancel a batch after receiving a 1205 deadlock message, it is necessary because the server does not always terminate the batch in a deadlock situation. If the batch is not canceled, any attempt to submit a new batch can result in a DB-Library error 10038 "Results Pending."

You can also use the SET DEADLOCK_PRIORITY statement (LOW or NORMAL). SET DEADLOCK_PRIORITY controls how the session reacts when in a deadlock situation. If set to LOW, the process will be the preferred victim of a deadlock situation. If set to NORMAL, the session will use the default deadlock-handling method.

If a deadlock situation continues, it is often useful to use trace flag 1204 to gather more information. Trace flag 1204 prints out the deadlock chains and victim, as shown in this sample output:

*** Deadlock Detected ***
==> Process 7 chosen as deadlock victim
== Deadlock Detected at: 1998-09-10 16:39:29.17
== Session participant information:
SPID: 7 ECID: 0 Statement Type: UPDATE
Input Buf: update t1 set c1 = c1 where c1 = 2

SPID: 8 ECID: 0 Statement Type: UPDATE
Input Buf: update t1 set c1 = c1 where c1 = 1

== Deadlock Lock participant information:
== Lock: KEY: 2:117575457:1 (010001000000)
Database: tempdb
Table: t1
Index: i1
- Held by: SPID 7 ECID 0 Mode "S"
- Requested by: SPID 8 ECID 0 Mode "X"
== Lock: KEY: 2:117575457:1 (020002000000)
Database: tempdb
Table: t1
Index: i1
- Held by: SPID 8 ECID 0 Mode "S"
- Requested by: SPID 7 ECID 0 Mode "X"

This deadlock information can be interpreted as follows:

The first section displays the deadlock victim and time of deadlock along with the sessions involved in the deadlock. For each session, the current SPID, statement type, and a portion of the input buffer is displayed.
The second section displays details about the locks involved in the deadlock. From the output above, note that the deadlock involves key locks on table t1, index i1. The deadlock output shows which processes own the locks involved in the deadlock and which sessions are waiting for the locks to be granted as well as the associated lock modes.
The process that has generated the least amount of log volume will, by default, be chosen as the deadlock victim and be rolled back automatically. To influence which session is rolled back, set the DEADLOCK_PRIORITY for a session.

For more information about deadlocking, see "Deadlocking" in Microsoft SQL Server Database Developer's Companion.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"SET DEADLOCK_PRIORITY" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Trace Flags" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1505

Severity Level 14

Message Text

CREATE UNIQUE INDEX aborted because a duplicate key was found.
Most significant primary key is '%S_KEY'.

Explanation

This error occurs when you attempt to create a unique index and more than one row contains the duplicate value. Microsoft SQL Server has detected a uniqueness violation and cancels the CREATE INDEX operation.

For example, the creation for the index below would be canceled due to the uniqueness violation at row 3. The BUCHANAN STEVEN combination is already located in row 1.

TABLE: tblTest
Row strLastName strFirstName strCity strState
1 BUCHANAN STEVEN BOISE ID
2 SUYAMA MICHAEL BUTTE MT
3 BUCHANAN STEVEN SEATTLE WA
4 DAVOLIO NANCY SAN FRANCISCO CA

CREATE UNIQUE NONCLUSTERED INDEX
idxUniqueNames ON
tblText(strLastName, strFirstName)

Action

You must review your index objective. If your index does not need to be unique, remove the UNIQUE keyword and reissue the CREATE INDEX statement. However, if you still want to create a unique index, you must query the table in question and remove the rows in error. For more information about the CREATE INDEX statement, see "CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference.

To find the duplicate rows, issue a GROUP BY statement:

SELECT * FROM tblTest
GROUP BY strLastName, strFirstName

See Also

In This Volume

Errors 1000 - 1999

Information

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1508

Severity Level 14

Message Text

CREATE INDEX aborted because a duplicate row was found. Primary key
is '%S_KEY'.

Explanation

This error occurs when you attempt to create a clustered index and a duplicate row is encountered in the table.

The following example produces error 1508 when the index creation process reaches row 3, which is an exact duplicate of row 1:

TABLE: tblTest
Row strLastName strFirstName strCity strState
1 BUCHANAN STEVEN BOISE ID
2 SUYAMA MICHAEL BUTTE MT
3 BUCHANAN STEVEN BOISE ID
4 DAVOLIO NANCY SAN FRANCISCO CA

CREATE CLUSTERED INDEX
idxClusteredName ON
tblText(strLastName)

Action

You must decide whether to allow or prevent duplicate rows in the table. To allow duplicate rows, you should add the ALLOW_DUP_ROW keyword to the CREATE INDEX statement. Be cautious when using IGNORE_DUP_ROW, because it physically removes duplicate data from the table. Also note that when creating clustered indexes, the amount of space required can be 120 percent to 150 percent of the original table size. For more information, see "CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference.

The following example creates the clustered index while allowing the duplicate rows to remain in the table:

CREATE CLUSTERED INDEX
idxClusteredName ON
tblText(strLastName)
WITH ALLOW_DUP_ROW

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1510

Severity Level 17

Message Text

Sort failed. Out of space or locks in database '%.*ls'.

Explanation

This error occurs when you attempt to create an index and there is not enough space in the database to complete the operation or no more locks are currently available.

Creating indexes can require 1.2 times the original table size when building a clustered index (this amount is in addition to the table size, but just during the time that CREATE INDEX is processing). This space must be available in the indicated database or in the segment on which you were attempting to create the index.

Action

When there is not enough space in the database, you may be able to select a specific filegroup on which to place the index. To locate a specific filegroup and to check the size available on the filegroup, execute sp_helpfilegroup:

sp_helpfilegroup 'PRIMARY'

Or use the ALTER DATABASE statement to increase the overall database size. Note that after you increase the size of the database, you may not be allowed to decrease the size. For more information about creating and extending files or filegroups, and altering or moving databases, see "ALTER DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference.

If your database has no more locks available, execute sp_configure to increase the number of locks. This example increases the amount of locks to 10,000:

sp_configure 'locks', 10000
GO
RECONFIGURE
GO

For the configuration option to take effect, stop and restart the Microsoft SQL Server service. For user convenience, you may want to pause the service and allow current user activity to gracefully finish before officially stopping the service. For more information about starting, pausing, and stopping the SQL Server service, see "Starting, Pausing, and Stopping SQL Server" in Microsoft SQL Server Administrator's Companion.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_helpfilegroup" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1530

Severity Level 16

Message Text

CREATE INDEX with DROP_EXISTING was aborted because a row was out
of order. Most significant offending primary key is '%S_KEY'.
Explicitly drop and create the index instead.

Explanation

This error occurs when you try to create an index using the DROP_EXISTING clause of the CREATE INDEX statement on a column or columns containing data that is not in sorted order. When this error occurs, no index is created.

The DROP_EXISTING clause speeds creation of an index when the data in the table is already in sorted order. The space required to build a clustered index is less when the DROP_EXISTING clause is used.

The DROP_EXISTING clause speeds index creation only for clustered indexes or unique nonclustered indexes. Creating a nonunique, nonclustered index with the DROP_EXISTING clause may succeed, but there is no improvement in performance.

Action

Use either of these strategies:

Execute CREATE INDEX with the DROP_EXISTING clause.
Drop and re-create the index without any clauses.

You can also use the SORTED_DATA_REORG clause, which physically reorganizes the data.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1702

Severity Level 16

Message Text

CREATE TABLE failed because column '%.*ls' in table '%.*ls' exceeds
the maximum of %d columns.

Explanation

This error occurs when you try to create a table with more than 1,024 columns. The maximum number of definable columns per table is 1,024.

Column names must follow the rules for identifiers. They must be unique within a given table, but you can use the same column name in different tables in the same database.

Action

Reduce the number of columns in the table to 1,024 or less. For more information about creating tables, see "CREATE TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference.

See Also

In This Volume

Errors 1000 - 1999

Information

"Maximum Capacity Specifications" in Microsoft SQL Server Introduction

"Using Identifiers" in Microsoft SQL Server Database Developer's Companion

Error 1803

Severity Level 17

Message Text

CREATE DATABASE failed. Could not allocate enough disk space for a
new database on the named disks. Total space allocated must be at
least %d MB to a accommodate copy of the model database.

Explanation

This error occurs when there is not enough space on the device to create the tempdb database.

Action

The procedure for handling this error depends on whether the error occurred on a user database or on tempdb.

If the error occurred on a user database

Create the database on a different disk.

Delete some files to free space on the disk.

If the error occurred on tempdb

If you moved tempdb from one device back to any default device and you don't have enough space on the device (2 MB), Microsoft SQL Server will attempt to create tempdb on another device. If you do not have a device with at least 2 MB free, SQL Server will not start. Start SQL Server using the -f minimum configuration option.

You can create a new device with at least 2 MB or free at least 2 MB on the device. If you create a new device, make sure it is specified as a default device, as in this example:

sp_diskdefault new_device_name, defaulton

If no default is specified, SQL Server sends an "out of memory" message (you can ignore this message) and then moves tempdb to RAM automatically.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"sqlservr Application" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1814

Severity Level 10

Message Text

Could not create tempdb. If space is low, extend the amount of space
and restart.

Explanation

This error occurs when there is not enough space on the device to create the tempdb database.

Action

You can create a new device with at least 2 MB or free at least 2 MB on the device. If you create a new device, make sure it is specified as a default device, as in the following example:

sp_diskdefault new_device_name, defaulton

If no default device is specified, Microsoft SQL Server sends an "out of memory" message (you can ignore this message) and then moves tempdb to RAM automatically.

See Also

In This Volume

Errors 1000 - 1999

Error 1902

Severity Level 16

Message Text

Cannot create more than one clustered index on table '%.*ls'.
Drop the existing clustered index '%.*ls' before creating another.

Explanation

This error occurs when a table can have only one clustered index, but it can have many nonclustered indexes. Microsoft SQL Server uses the clustered index to sort rows so that their physical order is the same as their logical (indexed) order. The bottom (leaf level) of a clustered index contains the actual data pages of the table.

In a nonclustered index, the physical order of the rows is not the same as the indexed order. In a nonclustered index, the bottom (leaf level) contains pointers to rows on data pages, which creates an extra level between the index structure and the data itself.

Action

Use the sp_helpindex system stored procedure to examine the existing indexes on the table. If a clustered index already exists, you must drop it before creating another clustered index.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_helpindex" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1903

Severity Level 16

Message Text

%d is the maximum allowable size of an index. The composite index
specified is %d bytes.

Explanation

This error occurs when the sum of the lengths of the columns that make up the composite index exceeds 900 bytes.

A composite index is an index that uses from 2 to 16 columns. For example, the following shows a composite index using objects from the Northwind database:

USE Northwind
GO
CREATE INDEX CompanyNameCity
ON Customers (CompanyName, City)
GO

In this example, CompanyName is defined as nvarchar(40) and City as nvarchar(15). Each character requires 2 bytes of storage, so the total length of this composite index is 110 bytes (2* (40 + 15)). Note that when using varchar columns, the index will assume the maximum length of the varchar column to calculate the length of the composite index.

Action

Examine the lengths of the columns in your composite index and make sure that the total does not exceed 900 bytes. For more information about determining the storage size for different Microsoft SQL Server data types, see "Data Types" in Microsoft SQL Server Transact-SQL and Utilities Reference. For more information about indexes, see "CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference and "Creating an Index" in Microsoft SQL Server Database Developer's Companion.

See Also

In This Volume

Errors 1000 - 1999

Error 1904

Severity Level 16

Message Text

Cannot specify more than %d column names for index key list. %d
specified.

Explanation

This error occurs when more than 16 columns are used to create a composite index. A composite index is an index that uses from 2 to 16 columns.

The following example shows a composite index using objects from the pubs database:

USE pubs
GO
CREATE INDEX idxPubNameCity
ON publishers (pub_name, city)
GO

Action

Modify the CREATE INDEX statement to use no more than 16 columns. For more information, see "CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"Creating an Index" in Microsoft SQL Server Database Developer's Companion

Error 1910

Severity Level 16

Message Text

Cannot create more than %d indexes or column statistics on one table.

Explanation

This error occurs when either:

A table already has 250 indexes.

A table has a large number of indexes, and statistics are either being created or updated. Microsoft SQL Server makes an entry in sysindexes for each set of column-level statistics that are either created or updated, if the base column does not already have an index.

The following example produces this error message by updating statistics on a table with 250 indexes:

USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TestCols')
DROP TABLE TestCols
GO
DECLARE @counter int, @stmt varchar(255)
SET @counter = 1
CREATE TABLE TestCols
( 
c1 int IDENTITY(1, 1),
c2 char(255)
)
-- Creating 250 indexes.
WHILE (@counter < 251)
BEGIN
IF (@counter = 1)
CREATE UNIQUE CLUSTERED INDEX Ind1 ON TestCols (c1)
ELSE
BEGIN
SELECT @stmt = 'CREATE INDEX t1Ind' + 
LTRIM(STR(@counter)) + 
' ON TestCols (c1)'
EXEC (@stmt)
END
SET @counter = @counter + 1
END

-- Updating Statistics. Returns error 1910.
UPDATE STATISTICS TestCols (c2) WITH COLUMNS, FULLSCAN

Action

Either:

Modify the CREATE INDEX statement to use no more than 250 indexes.

Remove any unused, nonclustered indexes to create or update statistics successfully.

For more information, see "CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"sysindexes" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE STATISTICS" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 1916

Severity Level 16

Message Text

CREATE INDEX options %ls and %ls are mutually exclusive.

Explanation

This error occurs when you attempt to create an index with two mutually exclusive CREATE INDEX statement clauses. The index is not created when this error occurs.

This table shows when to use the CREATE INDEX clauses:

Index type	Clause
Unique clustered	IGNORE_DUP_KEY
Nonclustered	None
Unique nonclustered	IGNORE_DUP_KEY

Action

Select the correct clauses for the CREATE INDEX statement.

See Also

In This Volume

Errors 1000 - 1999

In Other Volumes

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Creating an Index" in Microsoft SQL Server Database Developer's Companion

Error 2501

Severity Level 16

Message Text

Could not find table named '%.*ls'. Check sysobjects.

Explanation

This error is usually caused by some form of corruption in the database. This often happens when a table is being dropped and a serious error occurs that causes the drop to fail.

Action

Executing DBCC CHECKDB and DBCC CHECKCATALOG can indicate other problems in the database. Restore your database from backup or contact your primary support provider for assistance.

You may also be able to use bcp to copy out other tables. But because this problem is often caused by other problems in the database, problems can occur when you copy out other tables.

See Also

In This Volume

Errors 2000 - 2999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKCATALOG" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 2511

Severity Level 16

Message Text

Table Corrupt: Object ID %d, Index ID %d. Keys out of order on page
%S_PGID, slots %d and %d.

Explanation

This error occurs when an index is not ordered correctly. The %S_PGID variable can be a data page (for clustered indexes), an index page, or a leaf page.

Action

Important If executing DBCC CHECKDB or DBCC CHECKTABLE with the REPAIR_REBUILD clause does not correct the index problem or if you are unsure what effect either of these DBCC statements with the REPAIR_REBUILD clause has on your data, contact your primary support provider.

Follow these steps:

Note the object ID.
If the object with the error is not a system table (its object ID is more than 100), continue with the next step.

If the object with the error is a system table (its object ID is less than 100), you cannot drop the index. Restore the database from a known clean backup.
Find the name of the index involved, as follows:
```
USE master
```

GO SELECT name FROM sysindexes WHERE indid = index_id AND id = object_id

Drop and re-create the index.
Run DBCC CHECKTABLE on the affected table to verify that all problems have been resolved at the table level.

If problems persist, contact your primary support provider. Have the output from either DBCC CHECKDB or DBCC CHECKTABLE available for review.

See Also

In This Volume

Errors 2000-2999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKTABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 2513

Severity Level 16

Message Text

Table Corrupt: Object ID %ld (object '%.*ls') does not match between
'%.*ls' and '%.*ls'.

Explanation

This error occurs when the DBCC CHECKCATALOG statement deletes a database object in one system table and this is not expected in another. Most often, this occurs when one or more rows in the syscolumns, syscomments, sysindexes, or sysdepends tables have no corresponding rows in sysobjects. This error can also occur if an operation affecting the system table, such as deletion of a user table, was interrupted.

Action

Although this error seldom interferes with database use, it is a good idea to restore the affected system table.

Warnings Severe problems can result from the direct manipulation of the system catalogs. Do not modify the system catalogs unless instructed to do so by your primary support provider.

Follow these steps to restore the consistency of the system tables:

Display the offending rows by executing a query in the problem database against the two tables mentioned in the message. For example, if the message reports one or more mismatches between syscolumns and sysobjects:
```
USE master
```

GO SELECT * FROM syscolumns WHERE syscolumns.id NOT IN (SELECT sysobjects.id FROM sysobjects)

Enable updates to system tables by enabling the allow updates configuration option.

Before modifying any system catalogs, be sure that you have a valid backup of the database. For more information about backup operations, see "Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion.

Warning Incorrect modification of the system catalogs can result in database corruption or data loss.
Make sure that the rows displayed in Step 1 correspond to reported 2513 errors, and then delete them from the first table mentioned in the message text.
If the number of rows affected by the delete does not match the number found in Step 1, roll back the transaction. If the numbers match, commit it.
To confirm that the mismatches are fixed, reexecute DBCC CHECKCATALOG. Then, disable the allow updates configuration option.

See Also

In This Volume

Errors 2000-2999

In Other Volumes

"allow updates Option" in Microsoft SQL Server Administrator's Companion

"BEGIN TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CHECKPOINT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKCATALOG" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DELETE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"osql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"RECONFIGURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

"SQL Server Startup Options" in Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sqlservr Application" in Microsoft SQL Server Transact-SQL and Utilities Reference

"syscolumns" in Microsoft SQL Server Transact-SQL and Utilities Reference

"syscomments" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysdepends" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysindexes" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysobjects" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 2535

Severity Level 16

Message Text

Table Corrupt: Page %S_PGID is allocated to object ID %d, index ID %d,
not to object ID %d, index ID %d found in page header.

Explanation

The DBCC CHECKALLOC statement detected a mismatch in the object ID between an allocation structure (extent) and sysindexes.

Note Occasionally, DBCC CHECKALLOC reports this error when no real error condition exists. Execute DBCC CHECKALLOC in single-user mode if you suspect the 2535 error is incorrect.

Action

Follow these steps:

Examine the index ID associated with the page number in the message to determine whether the error occurred on the table data or on an index.

Restore the database:
- If the object ID is less than or equal to 100, the error is on a system table. Restore the database from a clean backup.
- If the object ID is greater than 100, the error is on a user table.
- If this error occurs on table data (the index ID = 1), restore the database from a clean backup.
- If the error occurs on an index, you can usually correct it by dropping and re-creating the index. If dropping and re-creating the index is not feasible, or if you cannot drop the index, contact your primary support provider for assistance.

If the problem persists, contact your primary support provider for assistance. Have the output of the appropriate DBCC statements available for review.

See Also

In This Volume

Errors 2000-2999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKALLOC" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKTABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 2601

Severity Level 14

Message Text

Cannot insert duplicate key row in object '%.*ls' with unique index
'%.*ls'.

Explanation

This error occurs when you attempt to put duplicate index values into a column or columns that have a unique index. Microsoft SQL Server does not allow two rows to have the same index value (including NULL) in columns that have a unique index. SQL Server checks for duplicate values when the index is created and each time the table is modified using the INSERT or UPDATE statement.

Action

If you need duplicate index values in the indexed column(s), drop the unique index and create a nonunique index.

To use a unique index on data that contains duplicate values, you must change some values to prevent the duplication. You can change the data already in the table by using the SELECT or UPDATE statement, or you can change the data to be inserted.

See Also

In This Volume

Errors 2000-2999

In Other Volumes

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Creating an Index" in Microsoft SQL Server Database Developer's Companion

"DELETE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 2750

Severity Level 16

Message Text

Column or parameter #%d: Specified column precision %d is greater than
the maximum precision of %d.

Explanation

This error occurs when the precision of a float, decimal, or numeric column exceeds the maximum value for the specified data type.

For decimal and numeric data types, Microsoft SQL Server normally supports a maximum precision of 28 digits for compatibility with various front ends, such as Microsoft Visual Basic®. However, if a precision greater than 28 is desired, use the /p parameter of the sqlservr application from a Win32 command prompt. For more information about running the sqlservr application, see "sqlservr Application" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Action

Define the column to have a precision that falls within the allowable precision range for that data type.

See Also

In Other Volumes

"decimal and numeric" in Microsoft SQL Server Transact-SQL and Utilities Reference

"float and real" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Precision, Scale, and Length" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 2751

Severity Level 16

Message Text

Column or parameter #%d: Specified column scale %d is greater than the
specified precision of %d.

Explanation

This error occurs when the scale of a decimal or numeric column exceeds the precision value for that column.

For decimal and numeric data types, Microsoft SQL Server normally supports a maximum precision of 28 digits for compatibility with various applications. However, if a precision greater than 28 is desired, use the /p parameter of the sqlservr application from a Win32 command prompt. For more information about running the sqlservr application, see "sqlservr Application" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Action

Specify the precision with p, which must be between 1 and the maximum precision, and the scale with s, which must be between 0 and p. If you do not specify the precision, SQL Server uses a default precision of 18. If you do notspecify the scale, SQL Server uses a default scale of 0.

See Also

In This Volume

Errors 2000-2999

In Other Volumes

"decimal and numeric" in Microsoft SQL Server Transact-SQL and Utilities Reference

"float and real" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Precision, Scale, and Length" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 2812

Severity Level 16

Message Text

Could not find stored procedure '%.*ls'.

Explanation

An attempt was made to execute a stored procedure that does not exist. If the procedure does exist (it appears when sp_help is run with no parameters), the error might have occurred because you did not fully qualify the procedure name. If the procedure is not owned by the user attempting to execute it, and it is not owned by the database owner (dbo), all references to it must include the owner name. For example, suppose user1 creates a procedure called proc1. Any users must add the owner name before the procedure name, as shown in the following example:

EXECUTE user1.proc1

Naming conventions for database objects are as follows:

[[[server_name.][database_name].][owner_name].]object_name

The default value for database_name is the current database; the default value for owner_name is the current user. If the current user is not the owner, the current user must specify the owner name when using the procedure. Because the owner name is part of the object name, two different users can have procedures with the same object name in the same database (for example user1.proc1 and user2.proc1). For more information about naming conventions, see "Transact-SQL Syntax Conventions" in Microsoft SQL Server Transact-SQL and Utilities Reference

The only exceptions to this naming convention are system procedures, which can be executed from any database. System procedures reside in the master database, are owned by the system administrator, and have names that begin with sp_. System procedures reference the system tables for the current database.

Action

If you do not know who owns the procedure, use sp_help to display the owner. If you run sp_help without any parameters, it displays objects owned by other users. To determine which procedures exist in a database and who owns them, use the following:

USE master
GO
SELECT name,owner = USER_NAME(uid) 
FROM sysobjects
WHERE type = 'P'
GO

If the procedure does not appear in the output of this query, the procedure is either in a different database or does not exist.

If you do not own the procedure in question, you can avoid error 2812 by qualifying the procedure name with the owner name, as shown in the following example:

EXECUTE user1.proc1

For procedures used by many users of a database, it is usually easiest if the dbo creates the procedure. This allows any user to find the procedure without specifying an owner name.

If the procedure is not in the database where it is executed, you can avoid this error by fully qualifying the procedure name with the database name, as shown in the following example:

EXECUTE database_1.user1.proc1

The owner name is not needed if you or the dbo own the procedure. For example:

EXECUTE database_1..proc1

Execute permission must be provided so that other users can execute this procedure, but no permissions are required to see the text of the procedure.

If this error occurs on system procedures, it might be resolved by running Instmstr.SQL. This reinstalls all system procedures and initializes various other structures.

See Also

In This Volume

Errors 2000-2999

In Other Volumes

"EXECUTE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_help" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3023

Severity Level 16

Message Text

Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such
as CREATE FILE) operations on a database must be serialized. Reissue the
statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.

Explanation

These operations may not be performed at the same time as a backup operation:

File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE clauses; INSERT, UPDATE, or DELETE statements.
Shrink database or shrink file.
CREATE INDEX.
Nonlogged operations such as bulk load, SELECT INTO, WRITETEXT, and UPDATETEXT. The database option select into/bulkcopy must be enabled for these operations to be nonlogged.

If a backup is started when one of these operations is in progress, the backup ends. If a backup is running and one of these operations is attempted, the operation fails. For more information, see Remarks in "BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Action

Reissue the operation after the conflicting operation has completed.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"ALTER DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC SHRINKDATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCCSHRINKFILE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DELETE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"WRITETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3036

Severity Level 16

Message Text

Database '%ls' is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed.

Explanation

Your standby database has not been recovered and may not be backed up. Usually, you recover this database only if your primary fails and you switch operations to the standby. Until this occurs, rely on backups taken from the primary server.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

Error 3101

Severity Level 16

Message Text

Database in use. The system administrator must have exclusive use of the
database to run the restore operation.

Explanation

This error occurs when you attempt to load a database backup while users are accessing the database. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action

Wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not in the database being loaded when you issue the RESTORE DATABASE command. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

See Also

In This Volume

Errors 3000-3999

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3120

Severity Level 16

Message Text

The database you are attempting to restore was backed up under a
different sort order ID (%d) than the one currently running on this
server (%d), and at least one of them is a non-binary sort order.

Explanation

This error occurs when attempting to restore a database with a different sort order from that of the backed up database. This error occurs when using any sort order except binary sort order.

A database cannot be restored into Microsoft SQL Server using RESTORE DATABASE unless these items match between the database and the server:

Sort order
Collation sequence
Unicode locale ID
Unicode comparison style

Important SQL Server does not support concurrent mixed sort orders. Only the same sort order can be used by both the server and data to be imported or restored.

Action

Change the mismatched items to be the same. For more information, see "Changing the Character Set, Sort Order, or Unicode Collation" in Microsoft SQL Server Introduction.

For example, if the database uses sort order x *** *** and the backup to load uses sort order y, then use either bcp, BULK INSERT, or Data Transformation Services (DTS) to save the database in the x sort order. Then, reinstall SQL Server. After reinstalling SQL Server, load the database with the y sort order. Finally, use either bcp, BULK INSERT, or DTS to restore the data with the x sort order.

See Also

In This Volume

Errors 3000-3999

Error 3149

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Installing SQL Server" in Microsoft SQL Server Introduction

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Sort Order" in Microsoft SQL Server Introduction

Error 3143

Severity Level 16

Message Text

The data set on device '%ls' is not a SQL Server backup set.

Explanation

The backup being restored conforms to the Microsoft Tape Format, but is not a Microsoft SQL Server backup. The backup may have been written by another software product.

Action

To determine the contents of the backup, consider using RESTORE HEADERONLY.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"RESTORE HEADERONLY" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3149

Severity Level 16

Message Text

The database you are attempting to restore was backed up under a
different Unicode locale ID (%d) or Unicode comparison style (%d) than
the Unicode locale ID (%d) or Unicode comparison style (%d) currently
running on this server.

Explanation

This error occurs when attempting to restore a database with a different sort order from that of the backed up database. This error occurs when using any sort order except binary sort order.

A database cannot be restored using RESTORE DATABASE unless these items match between the database and the server:

Sort order
Collation sequence
Unicode locale ID
Unicode comparison style

Important Microsoft SQL Server does not support concurrent mixed sort orders. Only the same sort order can be used by both the server and data to be imported or restored.

Action

Change the mismatched items to be the same. For more information, see "Changing the Character Set, Sort Order, or Unicode Collation" in Microsoft SQL Server Introduction.

For example, if the database uses sort order x *** *** and the backup to load uses sort order y, then use either bcp, BULK INSERT, or Data Transformation Services (DTS) to save the database in the x sort order. Then, reinstall SQL Server. After reinstalling SQL Server, load the database with the y sort order. Finally, either bcp, BULK INSERT, or DTS to restore the data with the x sort order.

See Also

In This Volume

Errors 3000-3999

Error 3120

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Installing SQL Server" in Microsoft SQL Server Introduction

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Sort Order" in Microsoft SQL Server Introduction

Error 3154

Severity Level 16

Message Text

The backup set holds a backup of a database other than the existing
'%ls' database.

Explanation

The backup set is a backup of a database with the same name as the database to which you are restoring. However, the database being restored was created by a different CREATE DATABASE statement than the database in the backup set. Even though the databases have the same name, they are in fact different databases.

Action

Either overwrite the existing database by reissuing the RESTORE DATABASE command using the WITH REPLACE clause, or restore the backup set to a different database name. If you restore the backup set to a different database name, ensure that the files that will be created do not already exist and are not being used by another database. If you chose the wrong backup set to restore, select a backup of the existing database and restore it.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"CREATE DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3155

Severity Level 16

Message Text

The RESTORE operation cannot proceed because one or more files have been
added or dropped from the database since the backup set was created.

Explanation

You must begin the restore sequence by restoring a full database backup created after files were added or removed from the database. You may not roll forward across file creation or deletion operations.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3206

Severity Level 16

Message Text

No entry in sysdevices for backup device '%.*ls'. Update sysdevices and
rerun statement.

Explanation

You have attempted to use a logical device that is not a backup device.

Action

Either define the device using sp_addumpdevice, or refer to the physical device directly by specifying the TAPE = or DISK = syntax of the BACKUP statement.

See Also

In This Volume

Errors 3000-3999

Error 3209

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"sp_addumpdevice" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysdevices" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3209

Severity Level 16

Message Text

'%.*ls' is not a backup device. Check sysdevices.

Explanation

You have attempted to use a logical device that is not a backup device.

Action

Either define the device using sp_addumpdevice, or refer to the physical device directly by specifying the TAPE = or DISK = syntax of the BACKUP statement.

See Also

In This Volume

Errors 3000-3999

Error 3206

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"sp_addumpdevice" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysdevices" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3227

Severity Level 16

Message Text

The volume on device '%ls' is a duplicate of stripe set member %d.

Explanation

The media family spans multiple volumes. The restore operation has already processed the data on the volume specified in the error message.

Action

Remove the volume and insert the next volume which has not yet been processed.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3242

Severity Level 16

Message Text

The file on device '%ls' is not a valid Microsoft Tape Format backup set.

Explanation

The backup device does not contain data in Microsoft Tape Format.

Either:

The contents of the device were not written by the BACKUP statement.
The device contains a Microsoft SQL Server version 6.x database dump. SQL Server 7.0 cannot restore backups from earlier versions of SQL Server.
The contents of the device have been damaged.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3247

Severity Level 16

Message Text

The volume on device '%ls' has the wrong media sequence number (%d).
Remove it and insert volume %d.

Explanation

The media family spans multiple volumes. The restore operation expected to process the volume specified in the error message, but found a different volume value instead.

Action

Remove the specified volume and insert the volume with the requested value.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3249

Severity Level 16

Message Text

The volume on device '%ls' is a continuation volume for the backup set.
Remove it and insert the volume holding the start of the backup set.

Explanation

The media family spans multiple volumes. The backup set to be processed by the restore operation starts on an earlier volume than the one inserted into the named device.

Action

Remove the volume and insert the volume containing the start of the target backup set. To determine which backup sets are contained on which volumes, use RESTORE HEADERONLY.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"RESTORE HEADERONLY" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3251

Severity Level 10

Message Text

The RAID stream on device '%ls' has been finished. The device will now
be re-used for one of the remaining RAID streams.

Explanation

The restore operation allows a media set to be restored with fewer physical devices than was used to create it (also known as backing it up). This message means that the restore operation has completely processed the media family on the named device, and is now ready to reuse the device to restore one of the remaining media families.

Action

Remove the volume from the named device and insert the first volume of a media family that has not yet been processed.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3256

Severity Level 16

Message Text

The backup set on device '%ls' was aborted while it was being created
and is incomplete. RESTORE sequence is terminated abnormally.

Explanation

The BACKUP operation that created the backup set did not finish successfully. You may only restore backup sets that were created successfully. For example, the backup may have been terminated with an attention message. The backup set is not complete, and the restore operation must terminate.

Action

If you were restoring a database backup, restore a different database backup, and use log backups to roll forward. If you were restoring a log backup, apply the next log backup, the log backup made following the incomplete backup, instead.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3258

Severity Level 16

Message Text

The volume on device '%ls' belongs to a different media set.

Explanation

The volume on the named device does not belong to the same RAID media set as the other volumes being processed.

Action

For tape media sets, remove the offending volume and insert the next volume of the media family.

For disks, reissue the command. Name only those backup devices that were part of the same RAID media set.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3263

Severity Level 10

Message Text

Cannot use the volume on device '%ls' as a continuation volume. It is
sequence number %d of family %d for the current media set. Insert a new
volume, or sequence number %d of family %d for the current set.

Explanation

The RAID media set spans multiple volumes. Initializing the volume currently in the named device would destroy the integrity of the RAID media set because the volume has already been used as a member of the media set.

Action

Remove the volume and insert a fresh tape that can be overwritten.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3267

Severity Level 16

Message Text

Insufficient resources to create UMS scheduler.

Explanation

When attempting a backup or restore operation, this error message indicates that the server is too busy to perform the backup or restore operation.

Action

Retry the operation after reducing the server load.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 3414

Severity Level 10

Message Text

Database '%.*ls' (database ID %d) could not recover. Contact Technical
Support for further instructions.

Explanation

During startup, Microsoft SQL Server could not complete the recovery of the specified database.

Action

To determine why recovery failed, examine the error log for any errors prior to error 3414. It is important to examine errors that occurred before the first occurrence of error 3414 because subsequent attempts to start the server might not give the detailed error information you need to diagnose the problem. If you do not have sufficient information to recover from the previous errors, you can recover from known clean backups, or you can contact your primary support provider for assistance. Note that you cannot use the database until whatever caused the error has been corrected.

See Also

In This Volume

Errors 3000-3999

Reporting Errors to Your Primary Support Provider

Error 3604

Severity Level 10

Message Text

Duplicate key was ignored.

Explanation

This error occurs when you attempt to insert a row that has an index value that violates the uniqueness property (UNIQUE with IGNORE_DUP_KEY) on an existing index.

Microsoft SQL Server ignores the statement that caused the error and continues processing the transaction.

Action

No action is necessary unless you want to insert that row into the table. If so, you can drop and re-create the index without the UNIQUE clause, or you can change the data causing the uniqueness violation.

For more information about indexes, see "CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference and "Creating an Index" in Microsoft SQL Server Database Developer's Companion.

See Also

In This Volume

Errors 3000-3999

Error 3627

Severity Level 16

Message Text

Could not create worker thread.

Explanation

When attempting a backup or restore operation, this error message indicates that the server is too busy to perform the backup or restore operation.

Action

Retry the operation after reducing the server load.

See Also

In This Volume

Errors 3000-3999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 4207

Severity Level 16

Message Text

BACKUP LOG is not allowed while the select into/bulk copy option is
enabled. Use BACKUP DATABASE or disable the option using sp_dboption.

Explanation

This error occurs when you perform a nonlogged operation on a database, such as bcp or SELECT INTO, followed by a BACKUP LOG to a dump device. Because there is data in your database that has not been logged and the log therefore cannot be used to properly recover the database, the BACKUP LOG operation is not allowed. For example, if a user executes a SELECT INTO followed by
an UPDATE statement, the SELECT INTO operation is not logged,
and the UPDATE results cannot be recovered from the log.

Note that simply enabling the select into/bulkcopy option does not, in itself, cause this condition. The option must be set and a nonlogged operation executed before this error message is reported.

Backing up the database creates a new savepoint on which subsequent transaction logs are based. Therefore, backing up the database ensures that the nonlogged changes are recoverable and backing up the transaction log to a device is once again allowed.

Action

You must perform a BACKUP DATABASE. Disabling the select into/bulkcopy option using sp_dboption will not allow you to back up the log (even though the message says this is an option). To clear the 4207 error condition, you must back up the database.

You might need to use BACKUP LOG WITH TRUNCATE_ONLY until you can perform a full database backup. If you must do this, you lose the ability to recover up-to-the-minute data changes in case of a media failure.

See Also

In This Volume

Errors 4000-4999

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"SAVE TRANSACTION" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 4208

Severity Level 16

Message Text

BACKUP LOG is not allowed while the trunc. log on chkpt. option is
enabled. Use BACKUP DATABASE or disable the option using sp_dboption.

Explanation

If the trunc. log on chkpt. database option is enabled, the log is truncated when periodic checkpoints occur. Only full database and differential database backups are allowed because the log has been truncated and any log backups made would be unusable.

Action

To maintain your database by performing only full and differential database backups, leave the trunc. log on chkpt. database option enabled and use BACKUP DATABASE.

To maintain a full set of transaction log backups to guarantee that no change to the database is lost in the event of a failure, disable the trunc. log on chkpt. database option, backup the database using BACKUP DATABASE, and begin taking periodic transaction log backups.

See Also

In This Volume

Errors 4000-4999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 4214

Severity Level 10

Message Text

There is no current database backup. This log backup cannot be used to
roll forward a preceding database backup.

Explanation

To restore the database after failure, you must begin either with a full database backup, or with a partial or complete set of file backups. Either this database has never been backed up or a BACKUP LOG statement was executed after an ALTER DATABASE statement, which added or removed one or more files, was performed. Therefore, the log backup just completed is not useful.

Action

Perform a full database backup before backing up the log.

See Also

In This Volume

Errors 4000-4999

Troubleshooting Backing Up and Restoring

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

Error 4305

Severity Level 16

Message Text

This backup set cannot be restored because the database has not been
rolled forward far enough. You must first restore all earlier logs
before restoring this log.

Explanation

The restore operation found a gap between the last restore and the transaction log that you attempted to apply.

Action

Locate the missing, earlier transaction log backups and apply these first. Transaction logs must be restored in the same order in which they were backed up.

See Also

In This Volume

Errors 4000-4999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 4306

Severity Level 16

Message Text

The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or
WITH STANDBY for all but the final step.

Explanation

After the database has been recovered, no further restore operations may be performed.

Action

To recover a database, start the sequence over and use the NORECOVERY clause on all RESTORE statements except the last. If you are maintaining a standby server and want to bring up the database in read-only mode between restore operations, use the STANDBY clause of RESTORE instead of the NORECOVERY clause.

See Also

In This Volume

Errors 4000-4999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 4318

Severity Level 16

Message Text

File '%ls' has been rolled forward to LSN %.*ls. This log terminates at
LSN %.*ls, which is too early to apply the WITH RECOVERY option. Reissue
the RESTORE LOG statement WITH NORECOVERY.

Explanation

You have attempted to recover the database to its state at the time the current log backup was made. However, at least one file has been modified since this backup was created. Recovery is not possible because the database would be left in an inconsistent state.

Action

To recover the database to its most recent state, reissue the RESTORE LOG statement with the NORECOVERY clause and continue to apply transaction logs, recovering only when you have rolled far enough forward.

To recover the database to the point-in-time at the end of this log backup, use RESTORE DATABASE to restore the indicated file to an earlier state and roll it forward.

See Also

In This Volume

Errors 4000-4999

Troubleshooting Backing Up and Restoring

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 5013

Severity Level 16

Message Text

The master and model databases cannot have files added to them. ALTER
DATABASE was aborted.

Explanation

This error occurs when you attempt to extend either the master or model databases by adding database files.

Action

Extend the master or model databases only when necessary. The master database will grow automatically, if needed. If there is no room on the disk drive where the master files reside, either delete other files to make more disk space or replace the disk drive with a larger disk drive. If you need to expand the model database, ensure that there is available disk space on the disk drive where the model data files currently reside.

See Also

In This Volume

Errors 5000-5999

In Other Volumes

"ALTER DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Rebuilding the master Database" in Microsoft SQL Server Administrator's Companion

"Restoring the master Database from a Current Backup" in Microsoft SQL Server Administrator's Companion

Error 5701

Severity Level 10

Message Text

Changed database context to '%.*ls'.

Explanation

This is an informational message indicating that the database context has changed. This message is returned anytime a USE database statement is executed.

Action

None needed.

See Also

In This Volume

Errors 5000-5999

In Other Volumes

"USE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 5808

Severity Level 16

Message Text

Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE
WITH OVERRIDE statement to force this configuration.

Explanation

This error message occurs in one of these two situations:

When a user tries to use the sp_configure system stored procedure with the allow updates option
When a user tries to set a configuration parameter to a value that Microsoft SQL Server recognizes as likely to interfere with performance or smooth operation

When the allow updates option is enabled, SQL Server allows direct updates to the system tables, so any user who can log on as the SQL Server system administrator can update the system tables directly with ad hoc queries and can create stored procedures that update the system catalog. Incorrect changes to the system tables can cause unrecoverable database corruption or data loss.

Warning Severe problems can result from the direct manipulation of the system catalogs. Do not modify the system catalogs unless instructed to do so by your primary support provider.

Action

Warning Incorrect modification of the system catalogs can result in database corruption or data loss.

To modify system catalogs, use the osql utility to alter the allow updates system configuration setting.

Note Only the system administrator can alter the value for the allow updates system configuration setting.

See Also

In This Volume

Errors 5000-5999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"allow updates Option" in Microsoft SQL Server Administrator's Companion

"osql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"RECONFIGURE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

"SQL Server Startup Options" Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 6103

Severity Level 17

Message Text

Could not do cleanup for the killed process. Received message %d.

Explanation

This error message occurs when another error caused a user connection to terminate abnormally. The message number that caused this error will be printed in the error 6103 message.

Action

Examine and resolve the "received message" reported in this error message.

See Also

In This Volume

Errors 6000-6999

In Other Volumes

"How to kill a process (Enterprise Manager)" in SQL Server Books Online

"KILL" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7130

Severity Level 16

Message Text

%ls WITH NO LOG is not valid at this time. Use sp_dboption to set the
'select into/bulk copy' option on for database '%.*ls'.

Explanation

This error occurs upon executing either the UPDATETEXT or WRITETEXT statements, or either the dbupdatetext or dbwritetext routines in a database that does not have the sp_dboption system stored procedure's select into/bulkcopy option enabled. Because these are nonlogged operations, the option must be enabled.

Often, UPDATETEXT, WRITETEXT, dbupdatetext, and dbwritetext are run against a temporary table. The select into/bulkcopy option must be enabled in tempdb, or these will not run successfully.

Action

Enable the sp_dboption system stored procedure's select into/bulkcopy option for all databases affected by the query that contains a nonlogged operation. To do this, the system administrator and database owners should follow these steps:

Enable the option:
```
USE master
```

GO sp_dboption database_name, 'select into/bulkcopy',true GO USE database_name GO CHECKPOINT GO

Verify that the change is active:
```
sp_helpdb database_name
```

Execute the nonlogged operation.
When the nonlogged operation is complete, backup the database.

Caution After executing a nonlogged operation, do not back up the transaction log. Although the procedure may appear to succeed, you may have been backing up only empty text pages. Whenever you make nonlogged changes to your database, you must use the BACKUP DATABASE statement, because changes made by the nonlogged operation cannot be recovered from transaction logs.
Return the database to its original condition by disabling the select into/bulkcopy option:
```
USE master
```

GO sp_dboption database_name, 'select into/bulkcopy',false GO USE database_name GO CHECKPOINT GO

Verify that the change is active:
```
sp_helpdb database_name
```

See Also

In This Volume

Errors 7000-7999

In Other Volumes

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion

"CHECKPOINT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"dbupdatetext" in SQL Server Books Online

"dbwritetext" in SQL Server Books Online* *

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_helpdb" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"WRITETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7303

Severity Level 16

Message Text

Could not initialize data source object of OLE DB provider '%ls'. %ls

Explanation

This error message indicates that the OLE DB data source object could not be initialized.

Action

This error message can occur because of one of these problems.

Problem	Resolution
One of the initialization parameters specified in sp_addlinkedserver (data_source, location, provider_string, or catalog) is incorrect for this provider.	Verify that these parameters defined for the linked server, specified by executing sp_addlinkedserver, are correct for the given provider. Check the provider's documentation for the appropriate values for these parameters.
Login and password sent to the provider is invalid.	Verify that there is a valid login and password configured for the linked server and the current SQL Server login through sp_addlinkedsrvlogin.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_addlinkedsrvlogin" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7304

Severity Level 16

Message Text

Could not create a new session on OLE DB provider '%ls'.

Explanation

Unable to create a new session to the OLE DB data source.

Action

Check for errror messages returned by the provider and review provider documentation.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Accessing External Data Using Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7306

Severity Level 16

Message Text

Could not open table '%ls' from OLE DB provider '%ls'. %ls

Explanation

This error message is returned if the OLE DB provider does not support the interfaces and OLE DB properties required for the UPDATE, DELETE, and INSERT statements.

Action

In this scenario, a trace on OLE DB Errors would output additional information on the specific missing OLE DB support. For example, in the case of Microsoft SQL Server, the INSERT, UPDATE, and DELETE statements are not supported on the remote table if the table does not have a unique index defined on it. In this case, SQL Server Profiler would output the following information:

OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: 
[PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate 
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetChange VALUE=True 
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_UPDATABILITY VALUE=1 STATUS=DBPROPSTATUS_OK]

This error message indicates the status of each OLE DB property that was requested on the rowset opened against the table being updated. This information indicates that all properties required by SQL Server to perform this query, except the DBPROP_IRowsetChange property, were satisfied by the provider.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7314

Severity Level 16

Message Text

OLE DB provider '%ls' does not contain table '%ls'.

Explanation

Either the specified table or columns do not exist or the login used to connect to the provider does not have the required permissions on the table or columns.

This message occurs on case-sensitive servers.

Action

Verify that the table or columns specified exist. Verify that the appropriate permissions are granted on the table or columns specified.

If the table was originally created in Oracle without quotation marks, specify the table name in the distributed query using all uppercase letters. If the table was originally created in Oracle with quotation marks, specify the table name in the distributed query using all lowercase letters.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"OLE DB Providers Tested with SQL Server" in Microsoft SQL Server Database Developer's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7321

Severity Level 16

Message Text

An error occurred while preparing a query for execution against OLE DB
provider '%ls'. %ls

Explanation

Indicates a possible syntax error in the pass-through query's query string parameter.

Action

Verify that the query string is free of syntax errors (with respect to the query language supported by the OLE DB provider).

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"OPENQUERY" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7356

Severity Level 16

Message Text

OLE DB provider '%ls' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.

Explanation

This error indicates that there was inconsistent metadata reported by the provider on a given table between compilation time and execution time of the query. This typically occurs because the provider returns inconsistent metadata between the OLE DB schema rowset COLUMNS (during compilation) and that metadata reported by the IColumnsInfo interface on the table's rowset.

Action

Consult SQL Server Profiler to determine which table column caused this error.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"Monitoring with SQL Server Profiler" in Microsoft SQL Server Administrator's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7357

Severity Level 16

Message Text

Could not process object '%ls'. The OLE DB provider '%ls' indicates that
the object has no columns.

Explanation

Either the specified table or columns do not exist or the login used to connect to the provider does not have the required permissions on the table or columns.

Action

Verify that the table or columns specified exist. Verify that the appropriate permissions are granted on the table or columns specified.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7391

Severity Level 16

Message Text

The operation could not be performed because the OLE DB provider '%ls'
does not support distributed transactions.

Explanation

This error can occur while processing an INSERT, UPDATE, or DELETE statement inside an explicit or implicit transaction. This indicates that the OLE DB provider does not support distributed transactions, which is needed for data modification statements inside an explicit or implicit transaction. A data modification statement can be executed against such a provider only in the case where the statement is a transaction by itself.

Action

Verify that the OLE DB provider specified supports distributed transactions. If the provider does not support distributed transactions, rewrite the data modification statement not to use distributed transactions.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

Error 7392

Severity Level 16

Message Text

Could not start a transaction for OLE DB provider '%ls'.

Microsoft SQL Server also returns this error message if the provider is a SQL Server-specific provider:

Only one transaction can be active on this session.

Explanation

The OLE DB provider returned error 7392 because only one transaction can be active for this session. This error indicates that a data modification statement is being attempted against an OLE DB provider when the connection is in an explicit or implicit transaction, and the OLE DB provider does not support nested transactions. SQL Server requires this support so that, on certain error conditions, it can terminate the effects of the data modification statement while continuing with the transaction.

Action

SET XACT_ABORT is ON. This causes SQL Server to terminate the surrounding transaction when there is an error while processing the data modification statement. If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB provider.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"SET XACT_ABORT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7399

Severity Level 16

Message Text

OLE DB provider '%ls' reported an error. %ls

Cannot start your application. The workgroup information file is missing
or opened exclusively by another user.

Explanation

This error message returned by the Microsoft OLE DB Provider for Jet indicates one of the following:

The Microsoft Access database is not a secured database and the login and password specified was not Admin with no password.
The Access database is secured and the HKEY_LOCAL_MACHINE \Software \Microsoft \Jet \4.0\SystemDB registry key is not pointing to the correct Access workgroup file. Secured Access databases have a corresponding workgroup file, including the full path, which should be indicated by the above registry key.

Action

Verify that there is a login mapping for the current Microsoft SQL Server login to Admin with no password.

If the Access database being accessed is secured, make sure that the above registry key points to the full pathname of the Access workgroup file.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7403

Severity Level 16

Message Text

Could not locate registry entry for OLE DB provider '%ls'.

Explanation

This error message indicates that either:

The OLE DB provider is not registered properly.

The name of the provider used in the provider_name parameter of sp_addlinkedserver (or specified in the OPENROWSET function) is incorrect.

Action

Verify that the provider has been registered correctly and that the provider_name parameter uses the PROGID of the provider.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"OPENROWSET" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 7413

Severity Level 16

Message Text

Could not perform a Windows NT authenticated login because delegation is
not available.

Explanation

This error message indicates that a distributed query is being attempted for a Microsoft Windows NT authenticated login without an explicit login mapping. In an operating system environment where security delegation is not supported, Windows NT authenticated logins need an explicit mapping to a remote login and password created using sp_addlinkedsrvlogin.

Action

Create explicit mapping to a remote login and password using sp_addlinkedsrvlogin.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"sp_addlinkedserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8101

Severity Level 16

Message Text

An explicit value for the identity column in table '%.*ls' can only be
specified when a column list is used and IDENTITY_INSERT is ON.

Explanation

You have attempted to insert a row containing a specific identity value into a table that contains an identity column. However, you did not provide a column list or have SET IDENTITY_INSERT enabled for the specified table.

Action

To insert a specific identity row in a table containing an identity column successfully you must provide a column list and SET IDENTITY_INSERT to ON. The following example inserts identity row 2, where iID is defined as the identity column.

Table: tblTest 
iID strData
1 King
3 Suyama

-- Enable IDENTITY_INSERT.
SET IDENTITY_INSERT tblTest ON
GO
-- Insert the specified identity row using a column list .
INSERT INTO tblTest (iID, strData) values (2, 'Davolio')
GO
-- Disable IDENTITY_INSERT .
SET IDENTITY_INSERT tblTest OFF
GO

See Also

In This Volume

Errors 8000-8999

In Other Volumes

"SET IDENTITY INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8102

Severity Level 16

Message Text

Cannot update identity column '%.*ls'.

Explanation

You have specifically attempted to alter the value of an identity column in the SET portion of the UPDATE statement. You can only use the identity column in the WHERE clause of the UPDATE statement.

Action

Updating of the identity column is not allowed. To update an identity column, you can use the following techniques:

To reassign all identity values, bulk copy the data out, and then drop and re-create the table with the proper seed and increment values. Then bulk copy the data back into the newly created table. When bcp inserts the values it will appropriately increase the values and redistribute the identity values. You can also use the INSERT INTO and sp_rename commands to accomplish the same action.

To reassign a single row you must delete the row and insert it using the SET IDENTITY_INSERT tblName ON clause.

See Also

In This Volume

Errors 8000-8999

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SET IDENTITY_INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_rename" in Microsoft SQL Server Transact-SQL and Utilities Reference

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8106

Severity Level 16

Message Text

Table '%.*ls' does not have the identity property. Cannot perform SET
operation.

Explanation

You have attempted to use the SET IDENTITY_INSERT property on a table that does not contain an identity column.

Action

Double-check the table in question by using the sp_help tblName stored procedure to verify the identity column information.

See Also

In This Volume

Errors 8000-8999

In Other Volumes

"SET IDENTITY_INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_help" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8114

Severity Level 16

Message Text

Error converting data type %ls to %ls.

Explanation

If the error text refers to both DBTYPE_DATE and datetime, respectively, this error message indicates that a DBTYPE_DATE OLE DB data type column from a remote table could not be converted to a datetime value in Microsoft SQL Server. The DBTYPE_DATE column most likely has a value outside the range supported by the datetime data type (datetime values must be from January 1, 1753, through December 31, 9999). Because the range of values of the DBTYPE_DATE data type is larger than that of the SQL Server datetime data type, such errors can occur if the column contains values outside of the range supported by SQL Server.

Action

Remove the remote table column of DBTYPE_DATE data type from the query select list or predicate list.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"datetime and smalldatetime" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8163

Severity Level 16

Message Text

The text, ntext, or image data type cannot be selected as DISTINCT.

Explanation

When querying, Microsoft SQL Server does not allow the use of SELECT DISTINCT on a text, ntext, or image column. For example, this query fails, returning error 8163:

USE Northwind
SELECT DISTINCT Description
FROM Categories

Action

Remove references to any text, ntext, or image columns when using SELECT DISTINCT.

See Also

In This Volume

Errors 8000-8999

In Other Volumes

"Eliminating Duplicates with DISTINCT" in Microsoft SQL Server Database Developer's Companion

"Query Fundamentals" in Microsoft SQL Server Database Developer's Companion

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8501

Severity Level 16

Message Text

MS DTC on server '%.*ls' is unavailable.

Explanation

This error can occur while processing an INSERT, UPDATE, or DELETE statement inside an explicit or implicit transaction. This error typically indicates that the MSDTC service is not running on the local server. Data modification statements in an explicit or implicit transaction require the MSDTC service to be running and the provider's support of distributed transactions.

Action

Use SQL Server Service Manager to verify that the MSDTC service has been started on the server. For more information, see your Microsoft Distributed Transaction Coordinator documentation.

See Also

In This Volume

Errors 7000 - 7999

In Other Volumes

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

Error 8645

Severity Level 17

Message Text

A time out occurred while waiting for memory resources to execute the
query. Re-run the query.

Explanation

If the query wait configuration option is -1, then Microsoft SQL Server waited 25 times the estimated query cost for the memory required to run the query. If query time is a nonnegative value, then SQL Server waited this amount of time, in seconds, for the memory required to run the query. The query timed out and it has not been executed.

Action

You can:

Add additional memory to the server.
Reduce the server workload.

To decrease the server workload, reduce the number of users currently using SQL Server. To prevent additional users from logging in to SQL Server, pause the server. For more information, see "Pausing and Resuming SQL Server" in Microsoft SQL Server Administrator's Companion.
Create one or more indexes.
Increase the query wait configuration value.

See Also

In This Volume

Errors 8000-8999

In Other Volumes

"Index Tuning Wizard" in Microsoft SQL Server Database Developer's Companion

"query wait Option" in Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8651

Severity Level 17

Message Text

Could not perform the requested operation because the minimum query
memory is not available. Decrease the configured value for the 'min
memory per query' server configuration option.

Explanation

SQL Server has computed the amount of memory that this query requires to complete. This amount of required memory is not currently available; the min memory per query option may be too high.

Action

Resubmit the query. If resubmitting the query does not allow the query to run, you can:

Add additional memory to the server.
Create one or more indexes.
Reduce the value of min memory per query.
Increase the query wait configuration option if it is a nonzero value.

See Also

In This Volume

Errors 8000-8999

In Other Volumes

"Index Tuning Wizard" in Microsoft SQL Server Database Developer's Companion

"min memory per query Option" in Microsoft SQL Server Administrator's Companion

"query wait Option" in Microsoft SQL Server Administrator's Companion

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8906

Severity Level 16

Message Text

Page %S_PGID in database ID %d is allocated in the SGAM %S_PGID and PFS
%S_PGID, but was not allocated in any IAM. PFS flags '%hs'.

Explanation

SQL Server has found an allocation error in the specified database.

Action

Either restore from a known clean backup or execute DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause. For example:

DBCC CHECKDB('pubs', REPAIR_ALLOW_DATA_LOSS)

If the error involves an index page, use the REPAIR_REBUILD clause. If the error involves a data page, it may be necessary to use the REPAIR_ALLOW_DATA_LOSS clause. In the likely event that you cannot allow the loss of data, you will need to restore from a known clean backup. If the problem persists, contact your primary support provider. Have the output from DBCC CHECKDB available for review.

If DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause does not resolve the allocation error, contact your primary support provider.

See Also

In This Volume

Errors 8000-8999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Managing Extent Allocations and Free Space" in Microsoft SQL Server Introduction

Error 8908

Severity Level 16

Message Text

Table Corrupt: Database ID %d, object ID %d, index ID %d. Chain linkage
mismatch. %S_PGID->next = %S_PGID, but %S_PGID->prev = %S_PGID.

Explanation

This error occurs when Microsoft SQL Server detects an inconsistency in the page linkage of one of the page chains associated with a table, for example, when a page's next pointer points to a page whose previous pointer points back to a different page. There is one doubly-linked page chain for the table data as well as one for each index level.

Important This is a serious error and must be corrected immediately.

If DBCC statements detect this error during run-time processing, error 605 will also occur.

Action

Determine which table is corrupt by examining the current page in the error message. Execute DBCC CHECKDB without a repair clause to determine the extent of the corruption. Then, execute DBCC CHECKDB with the appropriate repair clause to repair the corruption. If the page is associated with an index, it is sometimes possible to resolve the problem by dropping the index. In most cases, you must recover the database from a known clean backup.

You should also examine your operating-system error log file, in addition to the SQL Server error log, to determine if hardware errors might have caused the corruption.

If the problem persists, contact your primary support provider. Have the output from the DBCC CHECKDB statement available for review.

See Also

In This Volume

Errors 2000 - 2999

Errors 8000 - 8999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8925

Severity Level 16

Message Text

Table Corrupt: Cross object linkage: Page %S_PGID, slot %d, in object ID
%d, index ID %d, refers to page %S_PGID, slot %d, in object ID %d, index
ID %d.

Explanation

This error occurs when Microsoft SQL Server detects an inconsistency in the page linkage of one of the page chains associated with a table. One of the pages has been found to be linked in more than one page chain when it should be linked in only one chain. There is one doubly-linked page chain for the table data, as well as one for each index level.

Important This is a serious error and must be corrected immediately.

If DBCC CHECKDB without a repair clause detects this error during run-time processing, error 605 can also occur.

Action

Follow these steps to resolve the error:

Examine the index ID associated with the page number indicated in the message to determine whether the error occurred on the table data or on an index.

Restore the database:
- If the object ID is less than or equal to 100, the error is on a system table. Restore the database from a clean backup.
- If the object ID is greater than 100, the error is on a user table.
- If this error occurs on table data (the index ID = 0), restore the database from a clean backup.
- If the error occurs on an index, you can usually correct it by dropping and re-creating the index. If dropping and re-creating the index is not feasible, or if you cannot drop the index, contact your primary support provider for assistance.

If the problem persists, contact your primary support provider for assistance. Have the output of the appropriate DBCC statements available for review.

See Also

In This Volume

Errors 2000 - 2999

Errors 8000 - 8999

Reporting Errors to Your Primary Support Provider

Error 8952

Severity Level 16

Message Text

Table Corrupt: Database '%ls', index '%ls.%ls' (ID %d) (index ID %d).
Extra or invalid key for the keys:

Explanation

This error message indicates that one or more indexes are damaged and must be repaired or dropped.

Action

Repair indexes by executing DBCC CHECKDB with either the REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, or REPAIR_REBUILD clauses. To determine which repair clause best suits your needs, consult DBCC CHECKDB before executing it.

See Also

In This Volume

Error 8956

Errors 8000-8999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8956

Severity Level 16

Message Text

Index row (%d:%d:%d) with values (%ls) points to the data row identified
by (%ls).

Explanation

If Microsoft SQL Server returns this error message, it always follows error 8952.

This error message indicates that one or more indexes are damaged and must be repaired or dropped.

Action

See Also

In This Volume

Error 8952

Errors 8000-8999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 8976

Severity Level 16

Message Text

Table Corrupt: Object ID %d, index ID %d. Page %S_PGID not allocated or
corrupt. Parent %S_PGID and previous %S_PGID refer to it.

Explanation

This error means that a page is not allocated to an object that references it. When the index is used, Microsoft SQL Server returns either error 605 or error 823.

Important This error is serious. Data corruption is possible.

Pages encountering error 8976 may not be included in a database backup, because database backups are performed by reading allocation pages and not by traversing page chains. Correct this error before backing up the database.

Action

If a known clean backup is available, restore the database from the backup.

If no clean backup is available, use DBCC CHECKDB or DBCC CHECKFILEGROUP to determine the extent of the problem. Then, use DBCC CHECKDB with the appropriate repair clause to repair the damage. If the Object ID is 1 or 2 and the index ID is 1, or the table is sysfiles1, then DBCC CHECKDB cannot repair the page.

Important If either executing DBCC CHECKDB with one of the repair clauses does not correct the index problem or if you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact your primary support provider.

For indexes (0 < Index ID < 255)

The problem can be resolved by dropping and re-creating the index in question. After the index is rebuilt, run DBCC CHECKALLOC to verify that the problem no longer exists. If it persists, call your primary support provider.

See Also

In This Volume

Errors 8000-8999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"DBCC CHECKALLOC" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"DBCC CHECKFILEGROUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 9002

Severity Level 19

Message Text

The log file for database '%.*ls' is full. Back up the transaction log
for the database to free up some log space.

Explanation

The specified transaction log file has run out of free space.

Action

You can:

Free disk space on any disk drive containing the log file for the related database. Freeing disk space allows the recovery system to grow the log file automatically.

Free disk space on a different disk drive.
Move the transaction log files with an insufficient amount of free disk space to the disk drive in Step 1.
Detach the database by executing sp_detach_db.
Attach the database by executing sp_attach_db, pointing to the moved files.

Either add a log file to the specified database by using the ADD FILE clause of the ALTER DATABASE statement, or enlarge the log file by using the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Adding an additional log file allows the existing log to grow.

See Also

In This Volume

Errors 9000-9999

Insufficient Disk Space

In Other Volumes

"ALTER DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Expanding the Database" in Microsoft SQL Server Database Developer's Companion

"sp_attach_db" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_detach_db" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error 17050

Severity Level 16

Message Text

initerrlog: Could not open error log file '%1'. Operating system
error = %2.

Explanation

When installing Microsoft SQL Server on an NTFS partition, make sure that the NTFS file permissions allow read/write access. Otherwise, this error message may appear in the Microsoft Windows NT application log (for each installation attempt).

Action

Ensure that the NTFS file permissions allow read/write access. In addition, the SYSTEM account should have full-control rights to the computer. It is recommended that everyone using the computer have full-control rights, but that the NTFS partition not be shared.

See Also

In This Volume

Errors 17000 - 17999

Troubleshooting the Operating System

Error 17809

Severity Level 10

Message Text

Could not connect. The maximum number of '%1!ld!' configured user
connections are already connected. The system administrator can change
the maximum to a higher value using sp_configure.

Explanation

This error results when all configured user connections are in use and a request to create another Microsoft SQL Server process occurs. SQL Server is not configured with enough user connections to support all requests. There is one process slot structure (PSS) for each configured user connection.

Action

Use the sp_configure system procedure to increase the number of user connections:

Set the new number for user connections. Type:
```
sp_configure 'user connections', new_number
```

Instruct SQL Server to accept the values by using the RECONFIGURE statement:
```
RECONFIGURE
```

Restart SQL Server to activate the changes.

Depending on the exact configuration of your SQL Server installation, each user connection requires 37 KB of memory, whether or not it is in use. Gradually increase the number of user connections until this error no longer occurs. When you change the user connections parameter, you may need to increase the memory parameter.

See Also

In Other Volumes

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion

Error 17824 (Open Data Services)

Severity Level 16

Message Text

Could not write to ListenOn connection '%1!hs!', loginname '%2!ls!',
hostname '%3!ls!'. Connection closed.

Explanation

Usually indicates that the specified client disconnected.

Action

Inspect the Windows NT Event Viewer system and application logs for information that may indicate whether or not the problem is related to the server network protocol, network card, or system configuration. Review the Microsoft SQL Server-specific entries in the application log or the SQL Server error log for relevant network-related errors that may occur in conjunction with this error. If this review does not provide enough information to resolve the problem, special network monitoring tools and a review of the client's configuration may be needed.

If the problem persists, contact your primary network support provider for assistance.

See Also

In This Volume

Errors 17000 - 17999

Reporting Errors to Your Primary Support Provider

In Other Volumes

"How to view the Windows NT application log (Enterprise Manager)" in SQL Server Books Online

"How to view the Windows NT application log (Windows NT)" in SQL Server Books Online

"Viewing the Windows NT Application Log" in Microsoft SQL Server Administrator's Companion

Error 18456

Severity Level 14

Message Text

Login failed for user '%ls'.

Explanation

You do not have permission to log in to the server.

Action

Contact a member of the sysadmin fixed server role to request login permission.

See Also

In This Volume

Errors 18000 - 18999

In Other Volumes

"Logins" in Microsoft SQL Server Introduction

"Managing Security" in Microsoft SQL Server Administrator's Companion

Error 18458

Severity Level 14

Message Text

Login failed. The maximum simultaneous user count of %d licenses for
this server has been exceeded. Additional licenses should be obtained
and registered through the Licensing application in the Windows NT
Control Panel.

Explanation

This error occurs when the server is set for Per Server licensing and the number of attempted client connections exceeds the number of Client Access Licenses for this server.

Action

Obtain additional Client Access Licenses or reduce the number of simultaneous client connection attempts.

See Also

In This Volume

Errors 18000 - 18999

Error 18459

Level 14

Message Text

Login failed. The maximum workstation licensing limit for SQL Server
access has been exceeded.

Explanation

This error occurs when the server is set for Per Seat licensing and a connection is attempted from a client computer that does not have a Client Access License.

Action

Obtain a Client Access License for the client computer.

See Also

In This Volume

Errors 18000 - 18999

In Other Volumes

"SQL Server 7.0 Databases on the Desktop" in Microsoft SQL Server Introduction

"SQL Server 7.0 on Windows 95/98" in Microsoft SQL Server Introduction

"SQL Server Editions" in Microsoft SQL Server Introduction

Read/Write Error

Message Text

kernel: operating system error %d (%s) encountered

Explanation

Microsoft SQL Server failed to read from or write to the specified location on disk. This failure is usually a result of a physical disk problem like a bad sector on the disk drive or a failure of the disk drive or controller.

Action

Identify the device with the problem by selecting the row from sysfiles that has the same disk name indicated in the error message:

USE master
GO
SELECT name, filename 
FROM master..sysfiles
GO

The output from this query should provide the physical name of the damaged disk. Examine the disk as soon as possible and correct any problems. After the disk drive or controller problem is resolved, restore from a backup, if available. If no backup is available, execute DBCC CHECKDB with the REPAIR_DATA_LOSS clause to deallocate the damaged pages from the database.

Warning Using the REPAIR_ALLOW_DATA_LOSS clause of DBCC CHECKDB may cause a loss of data if the disk damage was to a data or text page. If the hardware problem is not corrected properly before executing DBCC CHECKDB, the damaged pages will be reallocated and this problem will continue.

After executing DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause, back up the repaired data, fix the hardware errors, and reload the previously backed up data.

See Also

In Other Volumes

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysfiles" in Microsoft SQL Server Transact-SQL and Utilities Reference

Server Is Unnamed

Message Text

server: server is unnamed

Explanation

This informational message is displayed during Microsoft SQL Server startup if the SQL Server installation has not been given a name using the sp_addserver system procedure. The server name referred to here is stored in the sysservers system table as well as in the @@SERVERNAME function. This name is used primarily for managing server-to-server communication and does not absolutely need to match the computer name of the server (but it usually should, for ease of use of replication and other multiple server actions).

Action

No action is required for this message. The system administrator can prevent this message by assigning a name to the server using the sp_addserver procedure, as follows:

sp_addserver sql_server_name, local

(Under Windows NT, the setup program automatically gives SQL Server the same name as the server computer.)

The @@SERVERNAME function will not reflect the name until SQL Server is restarted. Subsequently, each time SQL Server is started, the new name is displayed. For example, if the SQL Server is named MYSERVER, the message would read:

server: server name is 'MYSERVER'

Although the server name is used during server-to-server communications, specifying a name does not, in itself, facilitate this communication, and therefore does not represent a security risk. Naming all servers makes the SQL Server error logs easy to identify because the server name is contained within each log.

See Also

In Other Volumes

"@@SERVERNAME" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_addserver" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sysservers" in Microsoft SQL Server Transact-SQL and Utilities Reference

Error Log Messages

In reviewing the error log, you may see one or more of these messages.

Error message text	Explanation
Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE	This message is an informational error message indicating that the Microsoft Distributed Transaction Coordinator (MS DTC) service is either currently not running on the server, or is currently unavailable. For more information about MS DTC, see your Microsoft Distributed Transaction Coordinator documentation.
Warning: Server cursor memory usage: %d pages. If this message repeats, see the Error Log Messages topic in Troubleshooting.	For example: Warning: Server cursor memory usage: 531 pages. If this message repeats, see the Error Log Messages topic in Troubleshooting. Either too many cursors have been created and left open, or have not been deallocated. It is recommended that a cursor be closed and deallocated as soon as it is no longer needed. For more information, see "Cursors" in Microsoft SQL Server Database Developer's Companion. In time-critical situations, the system administrator may need to terminate those connections that have not been closing or deallocating cursors using KILL.
Warning: SQL cache memory usage: %d (pages). If this condition persists, see the Error Log Messages topic in Troubleshooting.	The SQL Server cache consumes memory and holds ad hoc and prepared SQL text. This message occurs if the SQL Server cache exceeds a certain number of pages of server memory. For example, this error occurs if one or more clients are preparing large numbers of SQL statements without performing corresponding unprepare operations. This lack of corresponding unprepare operations can be due to poor application design, an application bug, or repeated creation of prepared SQL text. This message will be printed again if server memory changes significantly and if the memory consumption of the SQL Server cache remains high. Either warn the suspected clients that server memory is at a low level, or terminate suspected connections using KILL.

See Also

In This Volume

Troubleshooting the SQL Server ODBC Driver

In Other Volumes

"KILL" in Microsoft SQL Server Transact-SQL and Utilities Reference

MAPI Error Messages

These MAPI error values are used by SQL Mail.

MAPI Constant	Description
MAPI_USER_ABORT	User abort.
MAPI_E_FAILURE	General MAPI failure.
MAPI_E_LOGIN_FAILURE	MAPI login failure.
MAPI_E_DISK_FULL	Disk full.
MAPI_E_INSUFFICIENT_MEMORY	Insufficient memory.
MAPI_E_ACCESS_DENIED	Access denied
MAPI_E_TOO_MANY_SESSIONS	Too many sessions.
MAPI_E_TOO_MANY_FILES	Too many files were specified.
MAPI_E_TOO_MANY_RECIPIENTS	Too many recipients were specified.
MAPI_E_ATTACHMENT_NOT_FOUND	A specified attachment was not found.
MAPI_E_ATTACHMENT_OPEN_FAILURE	Attachment open failure.
MAPI_E_ATTACHMENT_WRITE_FAILURE	Attachment write failure.
MAPI_E_UNKNOWN_RECIPIENT	Unknown recipient: Parameter '%s', recipient '%s'
MAPI_E_BAD_RECIPTYPE	Bad recipient type.
MAPI_E_NO_MESSAGES	No messages.
MAPI_E_INVALID_MESSAGE	Invalid message.
MAPI_E_TEXT_TOO_LARGE	Text too large.
MAPI_E_INVALID_SESSION	Invalid session.
MAPI_E_TYPE_NOT_SUPPORTED	Type not supported.
MAPI_E_AMBIGUOUS_RECIPIENT	A recipient was specified ambiguously.
MAPI_E_MESSAGE_IN_USE	Message in use.
MAPI_E_NETWORK_FAILURE	Network failure.
MAPI_E_INVALID_EDITFIELDS	Invalid edit fields.
MAPI_E_INVALID_RECIPS	Invalid recipients.
MAPI_E_NOT_SUPPORTED	Not supported.

See Also

In Other Volumes

"SQL Mail" in Microsoft SQL Server Administrator's Companion