SQL Server stores data in "pages" or allocation units of approximately 8,000 bytes. If you want to see the raw data in a given database page and interpret the information produced, you can dump that data to the console (or to the SQL Server log) using the undocumented DBCC PAGE command.
Here is the command syntax.
DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])
dbid|dbname: The name or dbid for the database to be used.
pagenum: The logical or virtual page number to show; 1 is the first.
print option: An optional parameter that controls how the data is formatted.
0 (default): prints out the page header information only, not the raw data.
1: prints out the page header, each row of information from the page, and the page's offset table (used for XXXXX).
2: does the same as 1, except the rows are printed as a single undelineated block of data.
cache: Optional parameter that indicates how caching is used.
0: forces DBCC PAGE to retrieve the page directly from disk -- not out of the cache kept by SQL Server.
1 (default): Uses a cached copy of the page if available. This option can help you determine if a particular page is being forced out of the cache or not.
logical: Optional parameter that determines what type of page to retrieve.
0: specifies that pagenum refers to a virtual page.
1: specifies that pagenum refers to a logical page.
DBCC PAGE requires that you set a trace flag before it can return values to the console. To do this, use DBCC TRACEON(3604) before running DBCC PAGE. If you want to send the results to the error log instead of the console, use DBCC TRACEON(3605).
Here's an example of the command at work, along with its output. Some of the flags in the output should be self-evident. For instance, if m_tornBits is anything but 1, then the "turn page" flag for that page has been set and the page is probably damaged.
DBCC TRACEON (3604)
DBCC PAGE (master, 1, 1)
PAGE: (1:1)
-----------
BUFFER:
-------
BUF ((Content component not found.)) x00EFA9C0
---------------
bpage = 0x4B8BE000 bhash = 0x00000000 bpageno = (1:1)
bdbid = 1 breferences = 1 bstat = 0xb
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page ((Content component not found.)) x4B8BE000
----------------
m_pageId = (1:1) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 99 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 1
m_freeCnt = 2 m_freeData = 8188 m_reservedCnt = 0
m_lsn = (288:743:4) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 1
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.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
Learning Center: Get several undocumented stored procedures
Tips: Check out our complete collection of stored procedures
Ask the Experts: Andrew Novick is available to answer your development questions
