SQL Server 7.0: Some Useful Undocumented DBCC Commands

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