🚀 go-pugleaf

Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands 
------------------------------------------------------------------------------
--
by Alexander Chigrik 
 

In this article, I want to tell you about some useful undocumented DBCC comman
ds, and how you can use these commands in SQL Server 7.0 and 2000 for administ
ering and monitoring.

DBCC is an abbreviation for Database Console Command. DBCC commands are genera
lly 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 demons
trate the effects of the command by displaying a trace of the output of the DB
CC command. It is not actually required to run the DBCC commands examined belo
w. If you run any of the DBCC commands below without the above option, the com
mand 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 c
ache.

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. 

  

DBCC DES

Prints the contents of the specified DES (descriptor).

Syntax: 

dbcc des [([dbid|dbname] [,objid|objname])] 

where: 

dbid|dbname - database id or the database name
objid|objname - object id or the object name

This is an example:

DBCC TRACEON (3604)
DBCC DES 

  

DBCC HELP 

DBCC HELP returns syntax information for the specified DBCC statement. In comp
arison with DBCC HELP command in version 6.5, it returns syntax information on
ly for the documented DBCC commands.

Syntax:

DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?') 

This is an example:

DBCC TRACEON (3604)
DECLARE @dbcc_stmt sysname
SELECT @dbcc_stmt = 'CHECKTABLE'
DBCC HELP (@dbcc_stmt) 

  

DBCC IND 

Shows all pages in use by indexes of the specified table.

Syntax:

dbcc ind(dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3}) 

where: 

dbid|dbname - database id or the database name 

objid|objname - object id or the object name 

printopt - print option

There is change in this command in how it is used in SQL Server 7.0, in that t
he printopt parameter is now no longer optional.

This is an example:

DBCC TRACEON (3604)
DBCC IND (master, sysobjects, 0) 

  

DBCC LOG

This command is used to view the transaction log for the specified database.

Syntax:

DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}]) 

where: 

dbid or dbname - Enter either the dbid or the name of the database

type - is the type of output, and includes these options:

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 curren
t transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID

by default, type = 0

To view the transaction log for the master database, run the following command
:

DBCC log (master) 

  

DBCC PAGE 

You can use this command to view the data page structure. 

Syntax: 

DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical]) 

where: 

dbid|dbname - Enter either the dbid or the name of the database

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 i
nformation. 

1 - This option causes DBCC PAGE to print out the page header information, eac
h row of information from the page, and the page's offset table. Each of the r
ows 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 s
ingle block of information rather than separating the individual rows. The off
set 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 t
han getting it from disk only.


logical - (Optional) This parameter is for use if the page number that is to b
e retrieved is a virtual page rather then a logical page. It can be either 0 o
r 1.

0 - If the page is to be a virtual page number.

1 - (Default) If the page is the logical page number.

This is an example:

DBCC TRACEON (3604)
DBCC PAGE (master, 1, 1)

  

DBCC PROCBUF

This command displays procedure buffer headers and stored procedure headers fr
om the procedure cache.

Syntax:

DBCC procbuf([dbid|dbname], [objid|objname], [nbufs], [printopt = {0|1}]) 

where: 

dbid|dbname - database id or the database name 

objid|objname - object id or the 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 pr
oc buff, proc header, and contents of buffer)

This is an example:

DBCC TRACEON (3604)
DBCC procbuf(master,'sp_help',1,0) 

  

DBCC PRTIPAGE

This command prints the page number pointed to by each row on the specified in
dex page.

Syntax:

DBCC prtipage(dbid, objid, indexid, indexpage) 

where: 

dbid - database ID 

objid - object ID 

indexid - index ID 

indexpage - the logical page number of the index page to dump 

This is an example:

DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sysobjects') 
DBCC prtipage(@dbid,@objectid,1,0) 

  

DBCC PSS

This command shows info about processes currently connected to the server.

Syntax:

DBCC pss(suid, spid, printopt = { 1 | 0 }) 

where: 

suid - server user ID 

spid - server process ID 

printopt - print option (0 standard output, 1 all open DES's and current seque
nce tree)

This is an example:

DBCC TRACEON (3604)
DBCC pss 

  

DBCC RESOURCE

This command shows the server's level RESOURCE, PERFMON and DS_CONFIG informat
ion. RESOURCE shows addresses of various data structures used by the server. P
ERFMON structure contains master..spt_monitor field info. DS_CONFIG structure 
contains master..syscurconfigs field information.

Syntax: 

DBCC resource 

This is an example:

DBCC TRACEON (3604)
DBCC resource 

  

DBCC TAB 

You can use the following undocumented command to view the data pages structur
e (in comparison with DBCC PAGE, this command will return information about al
l data pages for viewed table, not only for particular number)

Syntax:

DBCC tab (dbid, objid) 

where: 

dbid - is the database id 

objid - is the table id

This is an example:

DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sysdatabases')
DBCC TAB (@dbid,@objectid) 


--

�� ��Դ:�����Ļ����� http://bbs.cqupt.edu.cn��[FROM: bbs.cqupt.edu.cn]