Associating a Page with an Object

Some SQL Server error messages specify a logical page number instead of the table or index name to which the page belongs. The following procedure shows how to determine the object to which a particular database page belongs.

Although you can use DBCC CHECKALLOC and DBCC CHECKDB to identify table names and index IDs, a quicker method is described below. Note, however, that this method tells you only the table or index associated with a particular page number.

Suppose you encounter the following error message:

Error 644, Severity 21, State 1
The non_clustered leaf row entry for page 1342 row 6 was not found in index page 944 indexid 3 database 'production'
  

The error message implies that a nonclustered index is corrupt, but the corresponding table name or index name is not given ¾ only a page number (944) and an index ID (3).

To determine which table or index is involved, follow these steps:

1. Log in as sa.
2. Determine the database ID (dbid) as follows:

   select db_id('database_name')

3. Enable trace flag 3604 to allow DBCC output to appear on the server:

   dbcc traceon(3604) 

4. To display information about the page in question, use the DBCC PAGE statement as follows:

   dbcc page(database_id, page_number)

   Note The DBCC PAGE statement is not a supported feature, so future compatibility is not assured. It is offered here only for the purpose of providing a faster method of associating a page with an object. Additional information about the output of DBCC PAGE is not available.

   For example, assuming that the output from step 2 indicates that the database ID is 6, you can find information about page 944 (the index page indicated in the error message shown above) as follows. (The objid and indid shown in the boxes in the following example are used in steps 5 and 6, below.)

   dbcc page (6, 944)
   PAGE: Page not found in cache - read from disk.
   BUFFER: Buffer header for buffer 0x2c4b30 page=0x540800 bdnew=0x0 
   bold=0x0 bhash=0x0 bnew=0x0 bold=0x0 bvirtpg=7092 bdbid=6 
   bpinproc=0 bkeep=0 bspid=0 
   bstat=0x0000 bpageno=0
   PAGE HEADER: Page header for page 0x540800 
   objid=9051068
   pageno=944 nextpg=0 prevpg=0 timestamp=0001 
   000c70f2
   indid=3
   nextrno=32 level=0  freeoff=52 minlen=7 
   page status bits: 0x2, 
   DBCC execution completed. If DBCC printed error messages, see your System Administrator.

5. Translate the object ID (objid) in the PAGE HEADER section of the output into a table name:

   use database_name
   go
   select object_name(9051068)
   go
   --------------
   bad_table

6. If an index is applicable, translate the index ID (indid) into an index name:

   use database_name
   go
   select name from sysindexes 
   where id = 9051068 and indid = 3
   go

7. To determine the index type, refer to the following table:

Because the DBCC PAGE output indicated that the indid is 3, the page belongs to a nonclustered index. (If the indid is 0, the page belongs to a table, and it does not belong to an index.)

8. Disable trace flag 3604:

   dbcc traceoff(3604)