SQL Server 7.0: Some Useful Undocumented DBCC Commands



Introduction

Undocumented DBCC commands:

  • DBCC BUFFER

  • DBCC BYTES

  • DBCC DBINFO

  • DBCC DBTABLE

  • DBCC DES

  • DBCC HELP

  • DBCC IND

  • DBCC LOG

  • DBCC PAGE

  • DBCC PROCBUF

  • DBCC PRTIPAGE

  • DBCC PSS

  • DBCC RESOURCE

  • DBCC TAB

  • Literature

    Introduction


    In this article I want to tell you about some useful undocumented
    DBCC commands, and how you can use these commands in SQL Server 7.0
    for administering and monitoring.

    DBCC is an abbreviation of a DataBase Consistency Checker.
    This is the description of DBCC from SQL Server Books Online:


    A statement used to check the logical and physical consistency of a
    database, check memory usage, decrease the size of a database, check
    performance statistics, and so on. Database consistency checker (DBCC)
    ensures the physical and logical consistency of a database, but is not
    corrective.


    Undocumented DBCC commands


    1. DBCC BUFFER

    This command can be used to print buffer headers and pages from the
    buffer cache.
    Syntax:


    dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])

    where dbid|dbname – database id|database name.
    objid|objname – object id|object name
    nbufs – number of buffers to examine
    printopt – print option
    0 – print out only the buffer header and page header
    (default)
    1 – print out each row separately and the offset table
    2 – print out each row as a whole and the offset table

    This is the example:


    DBCC TRACEON (3604)
    dbcc buffer(pubs,’sysobjects’)

    2. DBCC BYTES

    This command can be used to dump out bytes from a specific address.
    Syntax:


    dbcc bytes ( startaddress, length )

    where startaddress – starting address to dump
    length – number of bytes to dump

    This is the example:


    DBCC TRACEON (3604)
    dbcc bytes (1000000, 100)

    3. DBCC DBINFO

    Print DBINFO structure for specified database.


    DBCC DBINFO [( dbname )]

    where
    dbname – is the database name.

    This is the example:


    DBCC TRACEON (3604)
    DBCC DBINFO (pubs)

    4. DBCC DBTABLE

    This command prints out the contents of the DBTABLE structure.
    Syntax:


    DBCC DBTABLE ({dbid|dbname})

    where
    dbid|dbname – database name or database ID

    This is the example:


    DBCC TRACEON (3604)
    DBCC DBTABLE (pubs)

    The DBTABLE structure has an output parameter called dbt_open.
    This parameter keeps track of how many users are in the database.

    Look at here for more details:
    FIX:
    Database Usage Count Does Not Return to Zero

    5. DBCC DES

    Prints the contents of the specified DES (descriptor).
    Syntax:


    dbcc des [( [dbid|dbname] [,objid|objname] )]

    where dbid|dbname – database id|database name.
    objid|objname – object id|object name

    This is the example:


    DBCC TRACEON (3604)
    DBCC DES

    6. DBCC HELP

    DBCC HELP returns syntax information for the specified DBCC statement.
    In comparison with DBCC HELP command in version 6.5, it returns syntax
    information only for the documented DBCC commands.

    Syntax:


    DBCC HELP (‘dbcc_statement’ | @dbcc_statement_var | ‘?’)

    This is the example:


    DBCC TRACEON (3604)
    DECLARE @dbcc_stmt sysname
    SELECT @dbcc_stmt = ‘CHECKTABLE’
    DBCC HELP (@dbcc_stmt)

    Look at here for more details:
    DBCC HELP
    (T-SQL)

    7. DBCC IND

    Shows all pages in use by indexes of the specified table.
    Syntax:


    dbcc ind( dbid|dbname, objid|objname, [printopt = { 0 | 1 | 2 }] )

    where
    dbid|dbname – database id|database name.
    objid|objname – object id|object name
    printopt – print option

    This is the example:


    DBCC TRACEON (3604)
    DBCC IND (pubs, authors)

    8. DBCC log

    This command is used to view the transactional log for the specified
    database.


    DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

    PARAMETERS:
    Dbid or dbname – Enter either the dbid or the name of the database
    in question.

    type – is the type of output:

    0 – minimum information (operation, context, transaction id)

    1 – more information (plus flags, tags, row length)

    2 – very detailed information (plus object name, index name,
    page id, slot id)

    3 – full information about each operation

    4 – full information about each operation plus hexadecimal dump
    of the current transaction log’s row.

    -1 – full information about each operation plus hexadecimal dump
    of the current transaction log’s row, plus Checkpoint Begin,
    DB Version, Max XACTID

    by default type = 0

    To view the transaction log for the master database, you can run the
    following command:


    DBCC log (master)

    9. DBCC PAGE

    You can use this command to view the data page structure.


    DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

    PARAMETERS:
    Dbid or dbname – Enter either the dbid or the name of the database
    in question.

    Pagenum – Enter the page number of the SQL Server page that is to
    be examined.

    Print option – (Optional) Print option can be either 0, 1, or 2.

    0 – (Default) This option causes DBCC PAGE to print
    out only the page header information.
    1 – This option causes DBCC PAGE to print out the
    page header information, each row of information
    from the page, and the page’s offset table. Each
    of the rows printed out will be separated from
    each other.
    2 – This option is the same as option 1, except it
    prints the page rows as a single block of
    information rather than separating the
    individual rows. The offset and header will also
    be displayed.

    Cache – (Optional) This parameter allows either a 1 or a 0 to be
    entered.
    0 – This option causes DBCC PAGE to retrieve the page
    number from disk rather than checking to see if it is
    in cache.
    1 – (Default) This option takes the page from cache if it
    is in cache rather than getting it from disk only.

    Logical – (Optional) This parameter is for use if the page number
    that is to be retrieved is a virtual page rather then a
    logical page. It can be either 0 or 1.

    0 – If the page is to be a virtual page number.
    1 – (Default) If the page is the logical page number.

    In this example one data page is viewed from the table titleauthor,
    database pubs.


    USE pubs
    GO
    DBCC TRACEON (3604)
    GO
    DECLARE @pgid int
    SELECT @pgid = first FROM sysindexes WHERE
    id = object_id(‘titleauthor’) AND indid = 1
    DBCC PAGE (pubs, @pgid, 1)
    GO

    This is the result from my computer:


    DATA:
    Offset 32 –
    011e9820: 04042000 3137322d 33322d31 31373650 .. .172-32-1176P
    011e9830: 53333333 33016400 0000051a 16150f04 S3333.d………


    Look at here for more details:
    Data
    page structure in MS SQL 6.5

    10. DBCC procbuf

    This command prints procedure buffer headers and proc-headers from
    the procedure cache.

    Syntax:


    DBCC procbuf( [dbid], [objid], [nbufs], [printopt = {0|1}] )

    where
    dbid – database ID
    objid – object ID
    nbufs – number of buffers to print
    printopt – print option
    (0 print out only the proc buff and proc header (default)
    1 print out proc buff, proc header and contents of buffer)

    This is the example:


    DBCC TRACEON (3604)
    DECLARE @dbid int, @objectid int
    SELECT @dbid = DB_ID(‘master’)
    SELECT @objectid = object_id(‘sp_help’)
    DBCC procbuf(@dbid,@objectid,1,0)

    11. DBCC prtipage

    This command prints the page number pointed to by each row on the
    specified index page.

    Syntax:


    DBCC prtipage( dbid, objid, indexid, indexpage )

    where
    dbid – database ID
    objid – object ID
    indexid – index ID
    indexpage – the logical page number of the index page to dump

    This is the example:


    DBCC TRACEON (3604)
    DECLARE @dbid int, @objectid int
    SELECT @dbid = DB_ID(‘pubs’)
    SELECT @objectid = object_id(‘authors’)
    DBCC prtipage(@dbid,@objectid,1,0)

    12. DBCC pss

    This command shows info about processes currently connected to the
    dataserver. Structure is the source of data contained in the sysprocesses
    table.

    Syntax:


    DBCC pss( suid, spid, printopt = { 1 | 0 } )

    where
    suid – server user ID
    spid – server process ID
    printopt – print option
    (0 standard output,
    1 all open DES’s and current sequence tree)

    This is the example:


    DBCC TRACEON (3604)
    dbcc pss

    13. DBCC resource

    This command shows dataserver level RESOURCE, PERFMON and DS_CONFIG
    info. RESOURCE shows addresses of various data structures used by
    the server. PERFMON structure contains master..spt_monitor
    field info. DS_CONFIG structure contains master..syscurconfigs
    field info.

    Syntax:


    DBCC resource

    This is the example:


    DBCC TRACEON (3604)
    DBCC resource

    14. DBCC TAB

    You can use the following undocumented command to view the data
    pages structure (in comparison with DBCC PAGE, this command will
    return information about all data pages for viewed table, not
    only for particular number)

    Syntax:


    DBCC tab (dbid, objid)

    where
    dbid – is the database id
    objid – is the table id

    This is the example:


    DBCC TRACEON (3604)
    DECLARE @dbid int, @objectid int
    SELECT @dbid = DB_ID(‘pubs’)
    SELECT @objectid = object_id(‘authors’)
    DBCC TAB (@dbid,@objectid)


    Literature


    1. “What are all the dbcc commands for SQL Server?” NTFAQ
    http://www.ntfaq.com/Articles/Index.cfm?ArticleID=14186

    2. INFO: Description of DBCC PAGE Command
    http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP

    3. THE UNAUTHORIZED DOCUMENTATION OF DBCC
    http://user.icx.net/~huntley/dbccinfo.htm

    4. The Totally Unauthorized List of Sybase DBCC Commands
    http://www.kaleidatech.com/dbcc1.htm

    5. The Totally Unauthorized List of Sybase DBCC Commands
    http://www.kaleidatech.com/dbcc2.htm

    6. The Totally Unauthorized List of Sybase DBCC Commands
    http://www.kaleidatech.com/dbcc3.htm

    7. FIX: Database Usage Count Does Not Return to Zero
    http://support.microsoft.com/support/kb/articles/Q175/3/03.ASP

    8. DBCC HELP (T-SQL)
    http://msdn.microsoft.com/library/psdk/sql/dbcc_10.htm


    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles