Yes, finally I come clean and tell all. It's an open secret that there's an undocumented DBCC command called DBCC PAGE that you can use to look at the contents of database pages. I've recommended in forum postings that people use it and Product Support also asks customers to use it during various investigations. Bottom line - it's there, and its extremely well tested (we use it extensively internally in literally thousands of tests). Bear in mind, however, that it is undocumented and thus unsupported - you won't get any help using if from Product Support. Can you use it on production systems? I don't see any reason why not but you should be wary, as with any undocumented command, procedure or trace flag.
Why am I doing this? I get asked this so much from people that are curious and I'd like to do some posts on interpreting CHECKDB results, which is a little hard unless you use DBCC PAGE.
So what's the syntax?
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354.
The printopt parameter has the following meanings:
-
0 - print just the page header
-
1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
-
2 - page header plus whole page hex dump
- 3 - page header plus detailed per-row interpretation
The per-row interpretation work for all page types, including allocation bitmaps.
By default, the output is sent to the errorlog. If you want the output to come back to your current connection, turn on trace flag 3604.
How do you find a page to dump? There are some easy ones - the allocation bitmaps. For example, lets dump the first PFS page in the database, just looking at its header:
DBCC
PAGE (master, 1, 1, 0);GO
PAGE: (1:1)
BUFFER:
BUF @0x02BB582C
bpage = 0x03772000 bhash = 0x00000000 bpageno = (1:1)
bdbid = 1 breferences = 1 bUse1 = 42182
bstat = 0xc0000b blog = 0x21598979 bnext = 0x00000000
PAGE HEADER:
Page @0x03772000
m_pageId = (1:1) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x3 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 2 m_freeData = 8188
m_reservedCnt = 0 m_lsn = (199:344:5) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 351018853
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There are a bunch of things on this that are beyond the scope of this post to explain - I'll get to them in future posts. One thing you'll notice is that there's some interpretation of the object and index IDs that are stamped on the page. This is because in SQL Server 2005, these are derived from the allocation unit ID, not the actual object and index ID. Simplistically, this is because of partitioning - each index can now have multiple b-trees, and hence multiple IAM chains. Each IAM chain is called an allocation unit. In the next post I'll go into some detail on how this is structured. Chicken-and-egg problem again, or a teaser to keep reading the blog :-)
Now how about if we ask for the detailed dump of the PFS page?
DBCC
PAGE (master, 1, 1, 3);GO
PAGE: (1:1)
BUFFER:
BUF @0x02BB582C
bpage = 0x03772000 bhash = 0x00000000 bpageno = (1:1)
bdbid = 1 breferences = 1 bUse1 = 42182
bstat = 0xc0000b blog = 0x21598979 bnext = 0x00000000
PAGE HEADER:
Page @0x03772000
m_pageId = (1:1) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x3 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 2 m_freeData = 8188
m_reservedCnt = 0 m_lsn = (199:344:5) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 351018853
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
PFS: Page Alloc Status @0x4414C000
(1:0) - (1:3) = ALLOCATED 100_PCT_FULL
(1:4) - (1:5) = NOT ALLOCATED 0_PCT_FULL
(1:6) - (1:7) = ALLOCATED 100_PCT_FULL
(1:8) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:9) - (1:10) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:11) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:12) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
<deleted to keep this post from being enormous>
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
How about being able to dump some pages from a user table? Firstly, you have to work out which pages comprise the table and index. Here's one way to do this (without having to divulge any more undocumented commands :-)
-
create an empty database
-
do a type 3 page dump of the first page PFS page in the database (1:1) using DBCC PAGE
-
creating a simple heap and insert a few rows into it
- do another PFS dump like in step 2 and you'll see some more pages have been allocated - these are the ones being used to store your new table. One of them will be the IAM page and another will be a data page.
Find some pages to dump and play about with DBCC PAGE. Experiment with adding indexes and LOB columns to see what different kinds of pages are created and look at the linkages between them. I'll go into what the various parts of the output mean in another post - in the meantime, if there's anything in particular you want to know, add a comment and I'll reply.
Have fun!
Microsoft