This post will demonstrate how to run the undocumented DBCC PAGE command to view the properties of a page. First we execute the DBCC TRACEON (3604) command to redirect the output to the SSMS console. Then we call the DBCC PAGE command itself to view the page. Notice the last parameter (0). ‘0’ outputs the least amount of information. The other values are 1,2, & 3. Be careful running 1 or 2 as it has a lot of output, including the binary data on the disk. 🙂
Lets run the first command to redirect the output to the console:
|
1 |
DBCC TRACEON (3604); |
…now lets get the page data itself:
|
1 2 3 4 5 |
DBCC PAGE ( 'AdventureWorks2012', --Database 1, --File Number 200, --Page Number 0) --Output Style (0-3 provides less to more output information) |
and the console output displays this:
PAGE: (1:200)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
BUFFER: BUF @0x0000000004287800 bpage = 0x000000013408A000 bhash = 0x0000000000000000 bpageno = (1:200) bdbid = 7 breferences = 0 bcputicks = 0 bsampleCount = 0 bUse1 = 36779 bstat = 0x9 blog = 0x15a bnext = 0x0000000000000000 PAGE HEADER: Page @0x000000013408A000 m_pageId = (1:200) m_headerVersion = 1 m_type = 2 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200 m_objId (AllocUnitId.idObj) = 34 m_indexId (AllocUnitId.idInd) = 2 Metadata: AllocUnitId = 562949955649536 Metadata: PartitionId = 562949955649536 Metadata: IndexId = 2 Metadata: ObjectId = 34 m_prevPage = (1:218) m_nextPage = (1:206) pminlen = 10 m_slotCnt = 91 m_freeCnt = 2414 m_freeData = 5596 m_reservedCnt = 0 m_lsn = (27:16:391) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -284057349 DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED ML (1:7) = NOT MIN_LOGGED |
Views – 2286