Problem solve Get help with specific problems with your technologies, process and projects.

Undocumented stored procedure: View SQL Server 2000 raw data

If you want to view and interpret raw data in your database, this undocumented command in SQL Server 2000 will enable you to dump data to the console or log. Contributor Serdar Yegulalp explains how to use the DBCC PAGE command in this tip.

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 @0x00EFA9C0
---------------
bpage = 0x4B8BE000        bhash = 0x00000000        bpageno = (1:1)
bdbid = 1                 breferences = 1           bstat = 0xb
bspin = 0                 bnext = 0x00000000        

PAGE HEADER:
------------

Page @0x4B8BE000
----------------
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


  • This was last published in August 2005

    Dig Deeper on SQL Server Stored Procedures

    Start the conversation

    Send me notifications when other members comment.

    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close