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:
select db_id('database_name')
dbcc traceon(3604)
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.
use database_name go select object_name(9051068) go -------------- bad_table
use database_name go select name from sysindexes where id = 9051068 and indid = 3 go
Index Id | Meaning |
---|---|
0 | Table data |
1 | Clustered index |
2 - 254 | Nonclustered index |
255 | Text page |
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.)
dbcc traceoff(3604)