Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands

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

DBCC is an abbreviation for Database Console Command. DBCC commands are generally used to check the physical and logical consistency of a database, although they are also used for a variety of miscellaneous tasks, as you will see here.

Note, the command:

DBCC TRACEON (3604)

is issued before each of the following DBCC examples in order to better demonstrate the effects of the command by displaying a trace of the output of the DBCC command. It is not actually required to run the DBCC commands examined below. If you run any of the DBCC commands below without the above option, the command runs, but you don't see what it is doing.

DBCC BUFFER 

This command can be used to display 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, which includes:

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 an example:

DBCC TRACEON (3604)
DBCC buffer(master,'sysobjects')

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 an example:

DBCC TRACEON (3604)
DBCC bytes (10000000, 100)

DBCC DBINFO 

Displays DBINFO structure for the specified database.

Syntax:

DBCC DBINFO [(dbname)]

where:

dbname - is the database name

This is an example:

DBCC TRACEON (3604)
DBCC DBINFO (master)

DBCC DBTABLE 

This command displays the contents of the DBTABLE structure.

Syntax:

DBCC DBTABLE ({dbid|dbname})

where:

dbid|dbname - database name or database ID

This is an example:

DBCC TRACEON (3604)
DBCC DBTABLE (master)

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