How to use DBCC PAGE

How to use DBCC PAGE

Rate This
  • Comments 11

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 :-)

  1. create an empty database
  2. do a type 3 page dump of the first page PFS page in the database (1:1) using DBCC PAGE
  3. creating a simple heap and insert a few rows into it
  4. 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!

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
  • TechEd is bringing some great things out of the Microsoft closet, Data Dude and Sql Everywhere being...
  • TechEd is bringing some great things out of the Microsoft closet, Data Dude and Sql Everywhere being...
  • In this entry I look at Foreign Key look ups and why you get blocking when the referenced table has a clustered index and no blocking when the table is a heap. I use Profiler to show locks acquired / released and we look at DBCC PAGE to identity what

  • Database locking is something that we should all have a good understanding of. ...

  • PingBack from http://nathanielsite.12gbfree.com/dbcc.html

  • PingBack from http://www.glorf.it/blog/2006/07/03/sql-talk/sql-server-storage-engine

  • Instant Initialization - What, Why and How?

  • PingBack from http://thestoragebench.info/story.php?id=6676

  • PingBack from http://fixmycrediteasily.info/story.php?id=9861

  • As always, simply thanks for the succinct way you communicate important data.

  • Hi Paul,

    DBCC CHECKDB on our databse gave the below error. From the below error i see the error is in object ID 60 "sys.objvalues".  This was happened on 22nd November when we ran DBCC CHECKDB.  How can this be resolved. Please suggest

    Executing the query "DBCC CHECKDB(N'****') WITH NO_INFOMSGS

    " failed with the following error: "Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -3212797581117423616 (type Unknown), page (40634:1181196049). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

    Incorrect PFS free space information for page (1:160) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 95_PCT_FULL, actual value 50_PCT_FULL.

    Incorrect PFS free space information for page (1:3427) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 80_PCT_FULL, actual value 95_PCT_FULL.

    Incorrect PFS free space information for page (1:22426) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 100_PCT_FULL, actual value 50_PCT_FULL.

    Incorrect PFS free space information for page (1:26439) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 50_PCT_FULL, actual value 100_PCT_FULL.

    Incorrect PFS free space information for page (1:27700) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 95_PCT_FULL, actual value 100_PCT_FULL.

    Incorrect PFS free space information for page (1:27701) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 80_PCT_FULL, actual value 95_PCT_FULL.

    Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data): Page (1:629823) could not be processed. See other errors for details.

    CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

    CHECKDB found 0 allocation errors and 7 consistency errors in table 'sys.sysobjvalues' (object ID 60).

    CHECKDB found 0 allocation errors and 8 consistency errors in database '****'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (****).

Page 1 of 1 (11 items)