Error 2559

Severity Level 16

Message Text

Data page number %ld is empty but is not the first page. Status = 0x%x.

Explanation

This error occurs when SQL Server encounters an empty page while traversing the page chain of an object that is not the first page of the chain. When an object is first created, a single, empty page is allocated to it. Usually there are no other empty pages.

If you are not encountering any other errors either at run time or from DBCC, it is possible that the empty page will not cause any further problems. Therefore, it is acceptable to wait to correct this problem until non-peak hours. If other errors are occurring, follow the procedures below or contact your primary support provider.

Action

Force the page chain involved to be rebuilt. This can be accomplished in different ways, depending on whether or not the page chain involved is associated with an index or with actual table data. To determine this, use the DBCC PAGE statement to determine the index ID (indid) and object name associated with the page number from the error message. For more information about DBCC PAGE, see "Associating a Page with an Object" in Chapter 24, "Additional Problem-solving Techniques."

Tables or clustered indexes (indid = 0 or indid = 1)

There are two ways to correct this error on tables or clustered indexes, depending on whether you have enough extra disk space to create a clustered index on the table in question. (Clustered index creation requires considerable disk space.) You can either drop the index and re-create it, or you can use bcp.

Dropping the index If a clustered index exists, drop and re-create it. If not, create a dummy clustered index and then drop it. This removes the problem because creation of a clustered index causes the table to be copied but does not copy the empty pages. For information about creating clustered indexes, see the Microsoft SQL Server Transact-SQL Reference.

Using bcp If you don't have enough extra disk space to create a clustered index on the table, you can use bcp to copy the table data out of SQL Server, remove all rows from the table, and then use bcp again to copy the table data back into SQL Server. Follow this procedure:

1. Use the bcp utility to copy the table data out of SQL Server into an operating-system file.
2. Empty the table using the TRUNCATE TABLE statement.
3. Drop all indexes on the table. (This step is optional.)
4. Use the bcp utility to copy the table data back into SQL Server.
5. Re-create all indexes on the table. (This step is optional.)

Nonclustered indexes (1 < indid < 255)

There are two ways to correct this error on a nonclustered index. You can either drop the index or create a clustered index.

Dropping the index

To drop the index, follow this procedure:

1. To determine the index name, execute the following query in the database in question:

   select name from sysindexes
   where id = object_id and indid = index_id

   In this example, object_id is the ID of the table and index_id is the indid of the page involved in this error (obtained using the DBCC PAGE statement).

2. Drop and re-create the nonclustered index identified in the results of the query.

Creating a clustered index

You can correct this error on a nonclustered index by creating a clustered index on the table, because creating a clustered index on a table forces all nonclustered indexes on the table to be rebuilt. Note, however, that this method might not be suitable for large tables because of the overhead required for creating clustered indexes.