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:


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])


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)


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.


DBCC DBINFO [(dbname)]


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})


dbid|dbname – database name or database ID This is an example: DBCC TRACEON (3604)

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



Leave a comment

Your email address will not be published.