다음을 통해 공유

How to use DBCC PAGE

  • 아티클

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!

Comments

  • Anonymous
    June 12, 2006
    TechEd is bringing some great things out of the Microsoft closet, Data Dude and Sql Everywhere being...

  • Anonymous
    June 12, 2006
    TechEd is bringing some great things out of the Microsoft closet, Data Dude and Sql Everywhere being...

  • Anonymous
    March 29, 2007
    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

  • Anonymous
    August 13, 2007
    Database locking is something that we should all have a good understanding of. ...

  • Anonymous
    May 27, 2008
    PingBack from http://nathanielsite.12gbfree.com/dbcc.html

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

  • Anonymous
    March 16, 2009
    Instant Initialization - What, Why and How?

  • Anonymous
    June 13, 2009
    PingBack from http://thestoragebench.info/story.php?id=6676

  • Anonymous
    June 16, 2009
    PingBack from http://fixmycrediteasily.info/story.php?id=9861

  • Anonymous
    January 30, 2014
    As always, simply thanks for the succinct way you communicate important data.

  • Anonymous
    November 26, 2014
    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 (****).

  • Anonymous
    July 15, 2015
    Once confusion, after create a new database, why shall check the “first page PFS page in the database (1:1)” of the Master Database? My question is, PFS file is Server scope of Database Scope? I can see PFS file is different in Master DB and my newly created DB.

  • Anonymous
    May 22, 2016
    dbcc page command can also used to analyze sql server database objects, read from here: http://dbathings.com/dbcc-page-command-analyze-sql-database-objects/