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 2000
for administering and monitoring.
DBCC is an abbreviation of a Database Console Command.
This is the description of DBCC from SQL Server Books Online:
__ATLAS_TABLE_BLOCK__0__Undocumented DBCC commands
1. DBCC BUFFER
This command can be used to print buffer headers and pages from the
buffer cache.
Syntax:
__ATLAS_TABLE_BLOCK__1__
where
dbid|dbname – database id|database name.
objid|objname – object id|object name
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
This is the example:
__ATLAS_TABLE_BLOCK__2__
2. DBCC BYTES
This command can be used to dump out bytes from a specific address.
Syntax:
__ATLAS_TABLE_BLOCK__3__
where
startaddress – starting address to dump
length – number of bytes to dump
This is the example:
__ATLAS_TABLE_BLOCK__4__
3. DBCC DBINFO
Print DBINFO structure for specified database.
Syntax:
__ATLAS_TABLE_BLOCK__5__
where
dbname – is the database name.
This is the example:
__ATLAS_TABLE_BLOCK__6__
4. DBCC DBTABLE
This command prints out the contents of the DBTABLE structure.
Syntax:
__ATLAS_TABLE_BLOCK__7__
where
dbid|dbname – database name or database ID
This is the example:
__ATLAS_TABLE_BLOCK__8__
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
5. DBCC DES
Prints the contents of the specified DES (descriptor).
Syntax:
__ATLAS_TABLE_BLOCK__9__
where dbid|dbname – database id|database name.
objid|objname – object id|object name
This is the example:
__ATLAS_TABLE_BLOCK__10__
6. DBCC HELP
DBCC HELP returns syntax information for the specified DBCC statement.
In comparison with DBCC HELP command in version 6.5, it returns syntax
information only for the documented DBCC commands.
Syntax:
__ATLAS_TABLE_BLOCK__11__
This is the example:
__ATLAS_TABLE_BLOCK__12__
Look at here for more details:
DBCC HELP
(T-SQL)
7. DBCC IND
Shows all pages in use by indexes of the specified table.
Syntax:
__ATLAS_TABLE_BLOCK__13__
where
dbid|dbname – database id|database name.
objid|objname – object id|object name
printopt – print option
There is change in this command in comparison with SQL Server 7.0 one:
you should specify printopt parameter (this parameter is not optional now)
This is the example:
__ATLAS_TABLE_BLOCK__14__
8. DBCC log
This command is used to view the transactional log for the specified
database.
Syntax:
__ATLAS_TABLE_BLOCK__15__
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, description)
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.
-1 – full information about each operation plus hexadecimal dump
of the current transaction log’s row, plus Checkpoint Begin,
DB Version, Max XDESID
by default type = 0
To view the transaction log for the master database, you can run the
following command:
__ATLAS_TABLE_BLOCK__16__
9. DBCC PAGE
You can use this command to view the data page structure.
Syntax:
__ATLAS_TABLE_BLOCK__17__
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.
This is the example:
__ATLAS_TABLE_BLOCK__18__
Look at here for more details:
Data
page structure in MS SQL 6.5
10. DBCC procbuf
This command prints procedure buffer headers and proc-headers from
the procedure cache.
Syntax:
__ATLAS_TABLE_BLOCK__19__
where
dbid|dbname – database id|database name.
objid|objname – object id|object name
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)
This is the example:
__ATLAS_TABLE_BLOCK__20__
11. DBCC prtipage
This command prints the page number pointed to by each row on the
specified index page.
Syntax:
__ATLAS_TABLE_BLOCK__21__
where
dbid – database ID
objid – object ID
indexid – index ID
indexpage – the logical page number of the index page to dump
This is the example:
__ATLAS_TABLE_BLOCK__22__
12. 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__23__
where
suid – server user ID
spid – server process ID
printopt – print option
(0 standard output,
1 all open DES’s and current sequence tree)
This is the example:
__ATLAS_TABLE_BLOCK__24__
13. 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__25__
This is the example:
__ATLAS_TABLE_BLOCK__26__
14. 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)
Syntax:
__ATLAS_TABLE_BLOCK__27__
where
dbid – is the database id
objid – is the table id
This is the example:
__ATLAS_TABLE_BLOCK__28__
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