The Totally Unauthorized List
of Sybase DBCC Commands

dbcc page ( dbid, pagenum [, printopt ][, cache ][, logical ] )

Prints the contents of the specified page.

Parameters
Param Description Valid Values
dbid database ID
pagenum the logical or virtual page number
printopt (optional) print option 0 print out only the buffer header and page header (default)
1 print buffer headers, page header, each row separately and the offset table
2 print out buffer and page headers, page as a whole and the offset table.
cache (optional) handling of pages in cache 0 print out the page as found on disk
1 print out the page as found in cache if it resides in cache, otherwise retrieve the page from disk and print (default).
logical (optional) type of page 0 pagenum is taken to a virtual page number
1 pagenum is taken to be a logical page number (default)

Parameters
Param	Description	Valid Values
dbid	database ID
pagenum	the logical or virtual page number
printopt	(optional) print option	0	print out only the buffer header and page header (default)
1	print buffer headers, page header, each row separately and the offset table
2	print out buffer and page headers, page as a whole and the offset table.
cache	(optional) handling of pages in cache	0	print out the page as found on disk
1	print out the page as found in cache if it resides in cache, otherwise retrieve the page from disk and print (default).
logical	(optional) type of page	0	pagenum is taken to a virtual page number
1	pagenum is taken to be a logical page number (default)

dbcc pglinkage ( dbid, start, number, printopt, target, order )

Display the page chain, performing integrity checks during traversal.

Parameters
Param Description Valid Values
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, or 0 if number is specified Specifying a target will continue to search in the specified direction until the target page or the end of the chain is found.
order traversal order 0 descending
1 ascending

Parameters
Param	Description	Valid Values
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, or 0 if number is specified	Specifying a target will continue to search in the specified direction until the target page or the end of the chain is found.
order	traversal order	0	descending
1	ascending

dbcc pktmemshow ( [spid] )

Lists network buffers for active processes.

Parameters
Param Description
spid (optional) server process ID

Parameters
Param	Description
spid	(optional) server process ID

dbcc procbuf ( [ dbid] [, objid] [, nbufs] [, printopt ] )

Print procedure buffer headers and proc-headers from the procedure cache.

Parameters
Param Description Valid Values
dbid (optional) database ID
objid (optional) object ID
nbufs (optional) number of buffers to print
printopt (optional) print option 0 print out only the proc buff and proc header (default)
1 print out proc buff, proc header and contents of buffer.

Parameters
Param	Description	Valid Values
dbid	(optional) database ID
objid	(optional) object ID
nbufs	(optional) number of buffers to print
printopt	(optional) print option	0	print out only the proc buff and proc header (default)
1	print out proc buff, proc header and contents of buffer.

dbcc procdiag ( dbid, option )

Produce diagnostics on procedure buffers

Parameters
Param Description Valid Values
dbid database ID
option diagnostic to run 0 display option menu
1 display diag bitmaps
2 display procbufs in cache
3 run summary diagnostics on entire cache

Parameters
Param	Description	Valid Values
dbid	database ID
option	diagnostic to run	0	display option menu
1	display diag bitmaps
2	display procbufs in cache
3	run summary diagnostics on entire cache

dbcc prtipage ( dbid, objid, indexid, indexpage )

Print the page number pointed to by each row on the specified index page.

Parameters
Param Description
dbid database ID
objid object ID
indexid index ID
indexpage the logical page number of the index page to dump

Parameters
Param	Description
dbid	database ID
objid	object ID
indexid	index ID
indexpage	the logical page number of the index page to dump

dbcc pss ( suid, spid, printopt )

Prints the contents of the specified PSS structure.

Parameters
Param Description Valid Values
suid (optional) server user ID
spid (optional) server process ID
printopt print option 0 standard output
1 all open DES's and current sequence tree
2 print all locks held by the process to the errorlog.

Parameters
Param	Description	Valid Values
suid	(optional) server user ID
spid	(optional) server process ID
printopt	print option	0	standard output
1	all open DES's and current sequence tree
2	print all locks held by the process to the errorlog.

dbcc rebuild_log ( dbname, rebuild, all )

Generates a new log containing a single checkpoint record.

Parameters
Param Description Valid Values
dbname database name
rebuild rebuild log flag 0 do not create a new log
1 create a new log
all remove log flag 0 old preserved log removed
1 old preserved and new log removed

Parameters
Param	Description	Valid Values
dbname	database name
rebuild	rebuild log flag	0	do not create a new log
1	create a new log
all	remove log flag	0	old preserved log removed
1	old preserved and new log removed

dbcc rebuildextents ( dbid, objid, indexid, sort, override )

Rebuild an object's extent chain.

Parameters
Param Description Valid Values
dbid database ID
objid object ID
indexid index ID
sort state of the sort bit 0 sorted
1 not sorted
override override option 0 rebuild only if database is not open
1 rebuild even though database is already open

Parameters
Param	Description	Valid Values
dbid	database ID
objid	object ID
indexid	index ID
sort	state of the sort bit	0	sorted
1	not sorted
override	override option	0	rebuild only if database is not open
1	rebuild even though database is already open

dbcc reindex ( tablename )

Rebuilds indexes.

Parameters
Param Description
tablename table whose indexes are to be rebuilt

Parameters
Param	Description
tablename	table whose indexes are to be rebuilt

dbcc remap ( objid, dbname, 1 )

Remaps a stored proc, trigger, rule, default, or view to be compatible with release 10.0.

Parameters
Param Description
objid object ID
dbname database name

Parameters
Param	Description
objid	object ID
dbname	database name

dbcc report_al [( dbname )]

Report on differences between allocation bitmaps and page chains.

Parameters
Param Description
dbname (optional) database name

Parameters
Param	Description
dbname	(optional) database name

dbcc resource

Print out the RESOURCE structure.

dbcc save_rebuild_log ( dbname )

Generate a new log containing a single checkpoint record and preserve old log extents by changing the object ID.

Parameters
Param Description
dbname database name

Parameters
Param	Description
dbname	database name

dbcc setkeepalive (#minutes)

For use on Novell TCP/IP only.

dbcc setreplicate ( replicate_name, dbccbit )

Update the replication status of the object in cache only.

Parameters
Param Description Valid Values
replicate_name object name
dbccbit new status 0 false
1 true

Parameters
Param	Description	Valid Values
replicate_name	object name
dbccbit	new status	0	false
1	true

dbcc settrunc ( "ltm", option [, generation_num] )

Replication Server related: modifies LTM (Log Transfer Manager) information for the database. "ltm" is entered as a literal string.

Options
Option Description Valid Values
truncation option Should log be allowed to be truncated. Can be either: "valid" set log truncation off -- The server cannot truncate tran log records that have not been transferred to Rep Server yet.
"ignore" allow log to be truncated (via dump tran with no_log, etc.)
generation id option resets the database generation number to generation_num. Used to increment number after a dump restore to prevent Rep Server from rejecting transactions as duplicates "gen_id", generation_num

Options
Option	Description	Valid Values
truncation option	Should log be allowed to be truncated. Can be either:	"valid"	set log truncation off -- The server cannot truncate tran log records that have not been transferred to Rep Server yet.
"ignore"	allow log to be truncated (via dump tran with no_log, etc.)
generation id option	resets the database generation number to generation_num. Used to increment number after a dump restore to prevent Rep Server from rejecting transactions as duplicates	"gen_id", generation_num

dbcc show_bucket ( dbid, page, lookup_type )

Look for specified page in the buffer cache.

Parameters
Param Description Valid Values
dbid database ID
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

Parameters
Param	Description	Valid Values
dbid	database ID
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

dbcc showtext ( db, page )

Displays text or image data contained on a specified page.

Parameters
Param Description
db database name or database ID
page logical page number

Parameters
Param	Description
db	database name or database ID
page	logical page number

dbcc tab ( dbid, objid, printopt )

Prints out the contents of each data page for the given table.

Parameters
Param Description Valid Values
dbid database ID
objid object ID
printopt print option 0 print out only the page header (default)
1 print out each row separately and the offset table
2 print out the page as a whole and offset table

Parameters
Param	Description	Valid Values
dbid	database ID
objid	object ID
printopt	print option	0	print out only the page header (default)
1	print out each row separately and the offset table
2	print out the page as a whole and offset table

dbcc tablealloc ( tablename [, option][, mode] )

Check data and index pages against corresponding extent structures for a table.

Parameters
Param Description Valid Values
tablename table name or table ID
option (optional) can take the following full fully check allocation
optimized check allocation pages listed in OAM (default)
fast exception report referenced but not allocated
null as optimized
mode (optional) can take the following fix fix errors on traversal (default for user tables)
nofix do not fix errors (default for system tables)

Parameters
Param	Description	Valid Values
tablename	table name or table ID
option	(optional) can take the following	full	fully check allocation
optimized	check allocation pages listed in OAM (default)
fast	exception report referenced but not allocated
null	as optimized
mode	(optional) can take the following	fix	fix errors on traversal (default for user tables)
nofix	do not fix errors (default for system tables)

dbcc thresholds ( db )

Provides thresholds information on the database.

Parameters
Param Description
db database name or database ID

Parameters
Param	Description
db	database name or database ID

dbcc traceoff ( tracenum [, tracenum ] ... )

Turn off specified trace flags.

Parameters
Param Description
tracenum number of the trace flag to turn off

Parameters
Param	Description
tracenum	number of the trace flag to turn off

dbcc traceon( tracenum [, tracenum ] ... )

Turn on specified trace flags.

Parameters
Param Description
tracenum number of the trace flag to turn on

Parameters
Param	Description
tracenum	number of the trace flag to turn on

Trace flags can be the following:

Trace Flags
Number Description
200 "Before" image of query tree
201 "After" image of query tree
260 To prevent "done_in_proc" messages
302 Information in index selection
310 Information in join selection
317 Complete information in join selection (voluminous)
320 Turn off join order heuristic
1204 Print out deadlock chains and victim
1205 Print out stack traces of deadlocked pids. Use in conjuction with 1204
2512 Bypass syslogs when doing checkalloc
1603 Use only standard unix calls for disk I/O
1610 Disable packet batching
3300 Display each log record as it is processed by recovery (voluminous)
3402 Do a alloc upgrade during loaddb recovery
3604 Send dbcc output to screen
3605 Send dbcc output to errorlog
3607 Do not recover any database, clear tempdb, or startup checkpoint
3608 Recover master only. Do not clear tempdb, or startup checkpoint
3609 Recover all databases. Do not clear tempdb, or startup checkpoint
3620 Do not kill infected processes
3701 Allows dropping and creating of system indexes (DANGEROUS!)
4000 Crash server at next loop for recovery testing
4012 Boot without starting up checkpoint process
4013 Place a record into errorlog each time someone logs in to the server
5101 Engine 0 will perform all disk and network I/O.
5102 Prevents engine 0 from running any non-affinitied tasks
8399 Enable descriptive names for groups (dbcc monitor)

Trace Flags
Number	Description
200	"Before" image of query tree
201	"After" image of query tree
260	To prevent "done_in_proc" messages
302	Information in index selection
310	Information in join selection
317	Complete information in join selection (voluminous)
320	Turn off join order heuristic
1204	Print out deadlock chains and victim
1205	Print out stack traces of deadlocked pids. Use in conjuction with 1204
2512	Bypass syslogs when doing checkalloc
1603	Use only standard unix calls for disk I/O
1610	Disable packet batching
3300	Display each log record as it is processed by recovery (voluminous)
3402	Do a alloc upgrade during loaddb recovery
3604	Send dbcc output to screen
3605	Send dbcc output to errorlog
3607	Do not recover any database, clear tempdb, or startup checkpoint
3608	Recover master only. Do not clear tempdb, or startup checkpoint
3609	Recover all databases. Do not clear tempdb, or startup checkpoint
3620	Do not kill infected processes
3701	Allows dropping and creating of system indexes (DANGEROUS!)
4000	Crash server at next loop for recovery testing
4012	Boot without starting up checkpoint process
4013	Place a record into errorlog each time someone logs in to the server
5101	Engine 0 will perform all disk and network I/O.
5102	Prevents engine 0 from running any non-affinitied tasks
8399	Enable descriptive names for groups (dbcc monitor)

dbcc tune ( "parameter", value [, tablename] )

Sets buildmaster parameters. Probably not necessary as of System11, where the old buildmaster parameters are now incorporated into sysconfigures (see documentation on sp_configure).

dbcc undo ( dbid, pgnum, rownum )

dbcc update_tmode ( procname, tranmode )

Update transaction-mode in both sysobjects and DES for stored procedures.

Parameters
Param Description Valid Values
procname stored procedure name
tranmode transaction mode unchained
chained
anymode

Parameters
Param	Description	Valid Values
procname	stored procedure name
tranmode	transaction mode	unchained
chained
anymode

dbcc usedextents ( dbid, devtype, display )

Count or display the status of used/unused extents on specified devices.

Parameters
Param Description Valid Values
dbid database ID
devtype type of device to check all show all extents
log show extents of allocation for the log
nonlog show extents of nonlog allocations
display what to report on 0 information about the extents
1 count of the extents

Parameters
Param	Description	Valid Values
dbid	database ID
devtype	type of device to check	all	show all extents
log	show extents of allocation for the log
nonlog	show extents of nonlog allocations
display	what to report on	0	information about the extents
1	count of the extents

Return to the Index
Return to the HomePage

The Totally Unauthorized List of Sybase DBCC Commands