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