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 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 ALLOCDUMP This command can be used to display all the 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)
DBCC BHASH This command can check the integrity of the buffer hash table, and optionally print it. 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
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 or the database name
objid|objname – object id or the 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)