SQL Server 6.5: Some Useful Undocumented DBCC Commands


Introduction

Undocumented DBCC commands:

  • DBCC allocdump

  • DBCC bhash

  • DBCC buffer

  • DBCC bytes

  • DBCC dbinfo

  • DBCC dbtable

  • DBCC delete_row

  • DBCC des

  • DBCC extentchain

  • DBCC extentcheck

  • DBCC extentdump

  • DBCC extentzap

  • DBCC findnotfullextents

  • DBCC help

  • DBCC ind

  • DBCC locateindexpgs

  • DBCC lock

  • DBCC log

  • DBCC page

  • DBCC pglinkage

  • DBCC procbuf

  • DBCC prtipage

  • DBCC pss

  • DBCC rebuildextents

  • DBCC resource

  • DBCC show_bucket

  • DBCC tab

  • DBCC undo

  • 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 6.5
    for administering and monitoring.

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


    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. DBCC helps ensure the physical and
    logical consistency of a database, but it is not corrective.



    Undocumented DBCC commands



    1. DBCC allocdump

    This command can be used to display all extents on an allocation page.

    Syntax:


    DBCC allocdump( dbid, page )

    where
    dbid – is the database id
    page – is the allocation page number

    Example:


    DBCC TRACEON (3604)
    DECLARE @dbid int, @pageid int
    SELECT @dbid = DB_ID(‘pubs’)
    SELECT @pageid = first FROM sysindexes WHERE
    id = object_id(‘titleauthor’) AND indid = 1
    DBCC allocdump(@dbid, @pageid)

    2. DBCC bhash

    This command can check integrity of and optionally print the buffer hash
    table.

    Syntax:


    DBCC bhash( { print_bufs | no_print }, bucket_limit )

    where
    print_bufs – display all buffers (default)
    no_print – display only buffers with problems
    bucket_limit – number of buffers allowed in a bucket (default = 0)

    Example:


    DBCC TRACEON (3604)
    DBCC bhash

    3. 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 =
    {0|1|2},buftype)

    where
    dbid|dbname – database name or database ID
    (0 will show data for all databases)
    objid|objname – object ID or object name
    (0 will show data for all objects)
    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
    buftype – buffer type to print

    Example:


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

    4. 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

    Example:


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

    5. DBCC DBINFO

    Print DBINFO structure for a specified database.

    Syntax:


    DBCC DBINFO [( dbname )]

    where
    dbname – is the database name.

    Example:


    DBCC TRACEON (3604)
    DBCC DBINFO (pubs)

    6. 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

    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

    7. DBCC delete_row

    This command can be used to delete an index or data row by either
    a row number or an offset on a page. DBCC delete_row is the nonloged
    command, so you can delete particular row without placed this row into
    transactional log. Use this command at your own risk!

    Syntax:


    DBCC delete_row ( dbid|dbname, page, delete_by_row, rownum )

    where
    dbid|dbname – database ID or database name
    page – logical page number
    delete_by_row – how delete: by row or by offset
    (1 – the next parameter is row number)
    (0 – the next parameter is offset on the page)
    rownum – row number or offset

    Example:


    SET NOCOUNT ON
    GO
    USE pubs
    GO
    DBCC TRACEON (3604)
    GO
    DECLARE @pgid int
    SELECT COUNT(*) FROM titleauthor
    SELECT @pgid = first FROM sysindexes WHERE
    id = object_id(‘titleauthor’) AND indid = 1
    SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
    DBCC delete_row (pubs, @pgid, ‘row’, 1)
    SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
    SELECT COUNT(*) FROM titleauthor

    8. DBCC des

    This command used to print the contents of the specified DES (descriptor).

    Syntax:


    DBCC des( [ dbid|dbname ][, objid ] )

    where
    dbid|dbname – database ID or database name
    objid – object ID

    Example:


    DBCC TRACEON (3604)
    DBCC DES

    9. DBCC extentchain

    This command shows extent header info for all extents in use by
    the specified object.

    Syntax:


    DBCC extentchain(dbid,objid,indexid,sort={1|0},display={1|0} [,order={1|0}])

    where
    dbid – database ID
    objid – object ID
    indexid – index ID
    sort – report on state of the sort bit
    (0 don’t include this info)
    (1 do report on sort bit status)
    display – what to report
    (0 display the count only)
    (1 display the extents)
    order – (optional) order to follow the chain
    (0 descending, 1 ascending)

    Example:


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

    10. DBCC extentcheck

    This command has the same output as extentchain and examine all extents
    on allocation pages for specified object.

    Syntax:


    DBCC extentcheck( dbid, objid, indexid, sort = {1|0} )

    where
    dbid – database ID
    objid – object ID
    indexid – index ID
    sort – state of the sort bit
    (0 don’t report this, 1 report sort bit status)

    Example:


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

    11. DBCC extentdump

    This command shows an extent dump.

    Syntax:


    DBCC extentdump( dbid, page )

    where
    dbid – database ID
    page – the number of a page controlled by the extent in question

    Example:


    DBCC TRACEON (3604)
    DECLARE @dbid int
    SELECT @dbid = DB_ID(‘pubs’)
    DBCC extentdump(@dbid, 1)

    12. DBCC extentzap

    This command can be used to clear all extents matching the parameter values.

    Syntax:


    DBCC extentzap( dbid, objid, indexid, sort )

    where
    dbid – database ID
    objid – object ID
    indexid – index ID
    sort – state of the sort bit

    Example:


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

    13. DBCC findnotfullextents

    This command shows extend id of extents allocated to the specified
    objid that are not full. If objid is specified, then an indexid
    can be specified, or all will do all of the indexes of table.
    If objid is all, then all database tables are done.

    Syntax:


    DBCC findnotfullextents( dbid,objid,indexid, sort = {1|0} )

    where
    dbid – database ID
    objid – object ID
    indexid – index ID
    sort – state of the sort bit

    Example:


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

    14. DBCC HELP

    DBCC HELP returns syntax information for the specified DBCC statement.

    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)

    15. DBCC ind

    This command shows all pages in use by indexes of the specified tabname.

    Syntax:


    DBCC ind( dbid|dbname, objid, printopt = { 0 | 1 | 2 } )

    where
    dbid|dbname – database ID or database name
    objid – object ID
    printopt – print option
    (0 print out only the buffer header and page header (default)
    1 print page headers, page data in row format, and offset
    tables
    2 print page headers, unformatted page data, and offset
    tables)

    Example:


    DBCC TRACEON (3604)
    declare @obid int
    SELECT @obid = object_id(‘authors’)
    DBCC ind (pubs, @obid, 1)

    16. DBCC locateindexpgs

    This command prints all references in the index to the specified page.

    Syntax:


    DBCC locateindexpgs( dbid, objid, page, indexid, level )

    where
    dbid – database ID
    objid – object ID
    page – logical page number of the page for which index
    references are being searched
    indexid – index ID
    level – level within the index to search for references

    Example:


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

    17. DBCC lock

    This command can be used to print out lock chains.

    Syntax:


    DBCC lock

    Example:


    DBCC TRACEON (3604)
    DBCC lock

    18. DBCC log

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


    DBCC log ( {dbid|dbname}, [, type={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.

    by default type = 0

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


    DBCC log (master)

    19. 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

    20. DBCC pglinkage

    This command can be used to display the page chain, performing
    integrity checks during traversal.

    Syntax:


    DBCC pglinkage(dbid,start,number,printopt={0|1|2},target,order={1|0})

    where
    dbid – database ID
    start – page number with which to start
    number – number of pages to examine, or 0 if target is specified
    printopt – print option
    (0 display only the count of pages scanned
    1 display information about the last 16 pages scanned
    2 display all page numbers in the scan)
    target – the particular page we are looking for
    order – traversal order
    (0 descending, 1 ascending)

    Example:


    DBCC TRACEON (3604)
    DBCC pglinkage(6,26,0,1,0,1)

    21. 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)

    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)

    22. 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

    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)

    23. 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)

    Example:


    DBCC TRACEON (3604)
    dbcc pss

    24. DBCC rebuildextents

    DBCC rebuildextents rebuilds an object’s extent chain.
    You should set READ ONLY option for your database, before run this command.

    Syntax:


    DBCC rebuildextents( dbid, objid, indexid )

    where
    dbid – database ID
    objid – object ID
    indexid – index ID

    Example:


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

    25. 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

    Example:


    DBCC TRACEON (3604)
    DBCC resource

    26. DBCC show_bucket

    This command shows hash bucket info for the specified pageid.

    Syntax:


    DBCC show_bucket( dbid|dbname, pageid, lookup_type )

    where
    dbid|dbname – database ID or database name
    page – logical page number of page being looked for
    lookup_type – how to conduct search
    (1 use hash algorithm to look in the bucket the page should be in
    2 scan the entire buffer cache)

    Example:


    DBCC TRACEON (3604)
    DBCC show_bucket (pubs, 1, 1)

    27. 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):


    DBCC tab (dbname, objname, printopt={ 0 | 1 | 2 })

    where dbname – is the database name,
    objname – is the table name,
    printopt – is the type of the output:

    0 – minimum information (only the pages headers, the total
    number of data pages in this table and the total number
    of data rows in this table)

    1 – more information (plus full rows structure)

    2 – as printopt = 1, but without rows separation (full dump)

    by default printopt = 0

    Example:


    DBCC TRACEON (3604)
    DBCC tab (pubs, ‘authors’)

    28. DBCC undo

    Syntax:


    DBCC undo( dbid, pageno, rowno )


    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

    9. INFO: Description of the DBCC PGLINKAGE Command
    http://support.microsoft.com/support/kb/articles/Q83/1/15.ASP


    »


    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