Introduction
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 of a DataBase Consistency Checker.
This is the description of DBCC from SQL Server Books Online:
__ATLAS_TABLE_BLOCK__0__Undocumented DBCC commands
1. DBCC allocdump
This command can be used to display all extents on an allocation page.
Syntax:
__ATLAS_TABLE_BLOCK__1__
where
dbid – is the database id
page – is the allocation page number
Example:
__ATLAS_TABLE_BLOCK__2__
2. DBCC bhash
This command can check integrity of and optionally print the buffer hash
table.
Syntax:
__ATLAS_TABLE_BLOCK__3__
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:
__ATLAS_TABLE_BLOCK__4__
3. DBCC buffer
This command can be used to print buffer headers and pages from the
buffer cache.
Syntax:
__ATLAS_TABLE_BLOCK__5__
where
dbid|dbname – database name or database ID
(0 will show data for all databases)
objid|objname – object ID or object name
(0 will show data for all objects)
nbufs – number of buffers to examine
printopt – print option
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
buftype – buffer type to print
Example:
__ATLAS_TABLE_BLOCK__6__
4. DBCC bytes
This command can be used to dump out bytes from a specific address.
Syntax:
__ATLAS_TABLE_BLOCK__7__
where
startaddress – starting address to dump
length – number of bytes to dump
Example:
__ATLAS_TABLE_BLOCK__8__
5. DBCC DBINFO
Print DBINFO structure for a specified database.
Syntax:
__ATLAS_TABLE_BLOCK__9__
where
dbname – is the database name.
Example:
__ATLAS_TABLE_BLOCK__10__
6. DBCC DBTABLE
This command prints out the contents of the DBTABLE structure.
Syntax:
__ATLAS_TABLE_BLOCK__11__
where
dbid|dbname – database name or database ID
Example:
__ATLAS_TABLE_BLOCK__12__
The DBTABLE structure has an output parameter called dbt_open.
This parameter keeps track of how many users are in the database.
Look at here for more details:
FIX:
Database Usage Count Does Not Return to Zero
7. DBCC delete_row
This command can be used to delete an index or data row by either
a row number or an offset on a page. DBCC delete_row is the nonloged
command, so you can delete particular row without placed this row into
transactional log. Use this command at your own risk!
Syntax:
__ATLAS_TABLE_BLOCK__13__
where
dbid|dbname – database ID or database name
page – logical page number
delete_by_row – how delete: by row or by offset
(1 – the next parameter is row number)
(0 – the next parameter is offset on the page)
rownum – row number or offset
Example:
__ATLAS_TABLE_BLOCK__14__
8. DBCC des
This command used to print the contents of the specified DES (descriptor).
Syntax:
__ATLAS_TABLE_BLOCK__15__
where
dbid|dbname – database ID or database name
objid – object ID
Example:
__ATLAS_TABLE_BLOCK__16__
9. DBCC extentchain
This command shows extent header info for all extents in use by
the specified object.
Syntax:
__ATLAS_TABLE_BLOCK__17__
where
dbid – database ID
objid – object ID
indexid – index ID
sort – report on state of the sort bit
(0 don’t include this info)
(1 do report on sort bit status)
display – what to report
(0 display the count only)
(1 display the extents)
order – (optional) order to follow the chain
(0 descending, 1 ascending)
Example:
__ATLAS_TABLE_BLOCK__18__
10. DBCC extentcheck
This command has the same output as extentchain and examine all extents
on allocation pages for specified object.
Syntax:
__ATLAS_TABLE_BLOCK__19__
where
dbid – database ID
objid – object ID
indexid – index ID
sort – state of the sort bit
(0 don’t report this, 1 report sort bit status)
Example:
__ATLAS_TABLE_BLOCK__20__
11. DBCC extentdump
This command shows an extent dump.
Syntax:
__ATLAS_TABLE_BLOCK__21__
where
dbid – database ID
page – the number of a page controlled by the extent in question
Example:
__ATLAS_TABLE_BLOCK__22__
12. DBCC extentzap
This command can be used to clear all extents matching the parameter values.
Syntax:
__ATLAS_TABLE_BLOCK__23__
where
dbid – database ID
objid – object ID
indexid – index ID
sort – state of the sort bit
Example:
__ATLAS_TABLE_BLOCK__24__
13. DBCC findnotfullextents
This command shows extend id of extents allocated to the specified
objid that are not full. If objid is specified, then an indexid
can be specified, or all will do all of the indexes of table.
If objid is all, then all database tables are done.
Syntax:
__ATLAS_TABLE_BLOCK__25__
where
dbid – database ID
objid – object ID
indexid – index ID
sort – state of the sort bit
Example:
__ATLAS_TABLE_BLOCK__26__
14. DBCC HELP
DBCC HELP returns syntax information for the specified DBCC statement.
Syntax:
__ATLAS_TABLE_BLOCK__27__
This is the example:
__ATLAS_TABLE_BLOCK__28__
Look at here for more details:
DBCC HELP
(T-SQL)
15. DBCC ind
This command shows all pages in use by indexes of the specified tabname.
Syntax:
__ATLAS_TABLE_BLOCK__29__
where
dbid|dbname – database ID or database name
objid – object ID
printopt – print option
(0 print out only the buffer header and page header (default)
1 print page headers, page data in row format, and offset
tables
2 print page headers, unformatted page data, and offset
tables)
Example:
__ATLAS_TABLE_BLOCK__30__
16. DBCC locateindexpgs
This command prints all references in the index to the specified page.
Syntax:
__ATLAS_TABLE_BLOCK__31__
where
dbid – database ID
objid – object ID
page – logical page number of the page for which index
references are being searched
indexid – index ID
level – level within the index to search for references
Example:
__ATLAS_TABLE_BLOCK__32__
17. DBCC lock
This command can be used to print out lock chains.
Syntax:
__ATLAS_TABLE_BLOCK__33__
Example:
__ATLAS_TABLE_BLOCK__34__
18. DBCC log
This command is used to view the transactional log for the specified
database.
__ATLAS_TABLE_BLOCK__35__
PARAMETERS:
Dbid or dbname – Enter either the dbid or the name of the database
in question.
type – is the type of output:
0 – minimum information (operation, context, transaction id)
1 – more information (plus flags, tags, row length)
2 – very detailed information (plus object name, index name,
page id, slot id)
3 – full information about each operation
4 – full information about each operation plus hexadecimal dump
of the current transaction log’s row.
by default type = 0
To view the transaction log for the master database, you can run the
following command:
__ATLAS_TABLE_BLOCK__36__
19. DBCC PAGE
You can use this command to view the data page structure.
__ATLAS_TABLE_BLOCK__37__
PARAMETERS:
Dbid or dbname – Enter either the dbid or the name of the database
in question.
Pagenum – Enter the page number of the SQL Server page that is to
be examined.
Print option – (Optional) Print option can be either 0, 1, or 2.
0 – (Default) This option causes DBCC PAGE to print
out only the page header information.
1 – This option causes DBCC PAGE to print out the
page header information, each row of information
from the page, and the page’s offset table. Each
of the rows printed out will be separated from
each other.
2 – This option is the same as option 1, except it
prints the page rows as a single block of
information rather than separating the
individual rows. The offset and header will also
be displayed.
Cache – (Optional) This parameter allows either a 1 or a 0 to be
entered.
0 – This option causes DBCC PAGE to retrieve the page
number from disk rather than checking to see if it is
in cache.
1 – (Default) This option takes the page from cache if it
is in cache rather than getting it from disk only.
Logical – (Optional) This parameter is for use if the page number
that is to be retrieved is a virtual page rather then a
logical page. It can be either 0 or 1.
0 – If the page is to be a virtual page number.
1 – (Default) If the page is the logical page number.
In this example one data page is viewed from the table titleauthor,
database pubs.
__ATLAS_TABLE_BLOCK__38__
This is the result from my computer:
__ATLAS_TABLE_BLOCK__39__
Look at here for more details:
Data
page structure in MS SQL 6.5
20. DBCC pglinkage
This command can be used to display the page chain, performing
integrity checks during traversal.
Syntax:
__ATLAS_TABLE_BLOCK__40__
where
dbid – database ID
start – page number with which to start
number – number of pages to examine, or 0 if target is specified
printopt – print option
(0 display only the count of pages scanned
1 display information about the last 16 pages scanned
2 display all page numbers in the scan)
target – the particular page we are looking for
order – traversal order
(0 descending, 1 ascending)
Example:
__ATLAS_TABLE_BLOCK__41__
21. DBCC procbuf
This command prints procedure buffer headers and proc-headers from
the procedure cache.
Syntax:
__ATLAS_TABLE_BLOCK__42__
where
dbid – database ID
objid – object ID
nbufs – number of buffers to print
printopt – print option
(0 print out only the proc buff and proc header (default)
1 print out proc buff, proc header and contents of buffer)
Example:
__ATLAS_TABLE_BLOCK__43__
22. DBCC prtipage
This command prints the page number pointed to by each row on the
specified index page.
Syntax:
__ATLAS_TABLE_BLOCK__44__
where
dbid – database ID
objid – object ID
indexid – index ID
indexpage – the logical page number of the index page to dump
Example:
__ATLAS_TABLE_BLOCK__45__
23. DBCC pss
This command shows info about processes currently connected to the
dataserver. Structure is the source of data contained in the sysprocesses
table.
Syntax:
__ATLAS_TABLE_BLOCK__46__
where
suid – server user ID
spid – server process ID
printopt – print option
(0 standard output,
1 all open DES’s and current sequence tree)
Example:
__ATLAS_TABLE_BLOCK__47__
24. DBCC rebuildextents
DBCC rebuildextents rebuilds an object’s extent chain.
You should set READ ONLY option for your database, before run this command.
Syntax:
__ATLAS_TABLE_BLOCK__48__
where
dbid – database ID
objid – object ID
indexid – index ID
Example:
__ATLAS_TABLE_BLOCK__49__
25. DBCC resource
This command shows dataserver level RESOURCE, PERFMON and DS_CONFIG
info. RESOURCE shows addresses of various data structures used by
the server. PERFMON structure contains master..spt_monitor
field info. DS_CONFIG structure contains master..syscurconfigs
field info.
Syntax:
__ATLAS_TABLE_BLOCK__50__
Example:
__ATLAS_TABLE_BLOCK__51__
26. DBCC show_bucket
This command shows hash bucket info for the specified pageid.
Syntax:
__ATLAS_TABLE_BLOCK__52__
where
dbid|dbname – database ID or database name
page – logical page number of page being looked for
lookup_type – how to conduct search
(1 use hash algorithm to look in the bucket the page should be in
2 scan the entire buffer cache)
Example:
__ATLAS_TABLE_BLOCK__53__
27. DBCC TAB
You can use the following undocumented command to view the data
pages structure (in comparison with DBCC PAGE, this command will
return information about all data pages for viewed table, not
only for particular number):
__ATLAS_TABLE_BLOCK__54__
where dbname – is the database name,
objname – is the table name,
printopt – is the type of the output:
0 – minimum information (only the pages headers, the total
number of data pages in this table and the total number
of data rows in this table)
1 – more information (plus full rows structure)
2 – as printopt = 1, but without rows separation (full dump)
by default printopt = 0
Example:
__ATLAS_TABLE_BLOCK__55__
28. DBCC undo
Syntax:
__ATLAS_TABLE_BLOCK__56__
Advertisement
Literature
1. “What are all the dbcc commands for SQL Server?” NTFAQ
http://www.ntfaq.com/Articles/Index.cfm?ArticleID=14186
2. INFO: Description of DBCC PAGE Command
http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP
3. THE UNAUTHORIZED DOCUMENTATION OF DBCC
http://user.icx.net/~huntley/dbccinfo.htm
4. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc1.htm
5. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc2.htm
6. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc3.htm
7. FIX: Database Usage Count Does Not Return to Zero
http://support.microsoft.com/support/kb/articles/Q175/3/03.ASP
8. DBCC HELP (T-SQL)
http://msdn.microsoft.com/library/psdk/sql/dbcc_10.htm
9. INFO: Description of the DBCC PGLINKAGE Command
http://support.microsoft.com/support/kb/articles/Q83/1/15.ASP