Using DBCC PAGE

If you're a systems administrator (sa), you can use the DBCC PAGE statement to view the page header, data rows, and row offset table for any data page in a database. But because people don't often need to view the content of a data page, you won't find much about DBCC PAGE in SQL Server Books Online (BOL). Also, as DBCC PAGE is an undocumented internal command, if you want the results of DBCC PAGE to return to your query output window (instead of writing to the errorlog), you need to turn on traceflag 3604 before running this command. To use DBCC PAGE, here's the syntax:

DBCC traceon(3604)DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache] )

The DBCC PAGE command includes the parameters that Table A shows.

The output from DBCC PAGE is divided into four main sections: Buffer, Page Header, Data, and Offset Table (really the Offset Array). The Buffer section shows information about the buffer for the given page. (A buffer in this context is an in-memory structure that manages a page.)

Reading the output takes practice. DBCC PAGE displays the data in the rows in groups of 4 bytes at a time. Within each group of four, the bytes are listed in reverse order. So, the first group of 4 bytes is byte 3, byte 2, byte 1, and byte 0.

To run the DBCC PAGE command to look at rows of data, you first need a page address for a data page. One way to get an address is to take a value from the column called first from the sysindexes table for a row representing a table (as opposed to a row for a nonclustered index). A row for a table will have an indid value of either 0, if the table is a heap, or 1, if the table has a clustered index. You can use a query like this one:

SELECT first FROM sysindexes WHERE id = object_id ('You then need to take the output from the above query, which will be a hexadecimal value such as 0xC70000000100 and convert it to file and page address. In hexadecimal notation, each set of two characters represents a byte. SQL Server displays the output of DBCC page with each set of 4 bytes in a byte-swapped format. So you first need to swap the bytes to get 00 01 00 00 00 C7. The first two groups represent the 2-byte file number, and the last four groups represent the page number. So the file is 0x0001, which is 1, and the page number is 0x000000C7, which is 199 in decimal. (You can use the Windows calculator to convert the values.)SQL Server 7.0 doesn't guarantee that the sysindexes.first column will always accurately identify the first page of a table. I've found that the first value is reliable immediately after you create a table and insert some rows, but it becomes inaccurate after you begin deleting and updating data in the table.