Some Useful Undocumented SQL Server 6.5 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 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)

Continues…

Pages: 1 2 3 4 5 6




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |