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.