Jaa

Using DBCC PAGE

  • Artikkeli

DBCC PAGE is an undocumented – and unsupported – DBCC statement in SQL Server for dumping the raw contents of a single database page. It will return all the metadata stored in the page, and with the right detail level parameter – return the user data as well. 

Since this is not documented and not supported – you use this at your OWN RISK.

The format in SQL Server 2008 is as follows:

DBCC PAGE ( [database name | database id], [fileid], [Page Number], [Optional Detail Level] )

Where “Detail Level” is:

0 – [Default] The header of the page, it’s allocation status (by referencing SQL allocation
pages like GAM, SGAM, PFS, DIFF, etc…), and metadata from the buffer holding the
page is dumped

1 – Level 0 plus the user data records organized by slot array position with only the raw
      memory dump of the record included

2 – Level 0 plus a raw memory dump of the entire data section of the page with the slot array
and all the offsets

3 – Level 0 plus the user data records organized by slot array position with a raw memory
dump of the record and the record’s columns converted and displayed in a readable
format

The data section is probably best analyzed using option 3. Though you can find the individual records using option 1, or by mapping the offsets listed using option 2, you won’t get much use out of it unless you were also very familiar with the SQL Server record format specification to decode the column data out of the raw memory dump of the record.

The header contains useful information to confirm your understanding of SQL Server internals, and troubleshooting issues. It contains information such as the Page ID, the object ID the page belongs to, the page type, previous and next pages in the chain, number of slots in the slot array, number of ghosted records, torn page values, last LSN to touch the page, and much more.

I’ve heard of some uses of negative parameters for the detail level parameter, but in SQL Server 2008 these all return the same as level 0 – which is the default.

This command is very useful for learning purposes, troubleshooting corruption, and documenting concepts in blog entries Smile.

- Jay