Useful ASE15.5 DBCC Commands**
addtempdb
Usage: dbcc addtempdb( dbid|dbname )
Adds a temporary database to the global list of available temporary databases. If the database does not exist or is not a temporary database, an error is generated. If the database is already a member of the list, an informational message prints.
affinity
Usage: dbcc affinity( 'push' | 'pop', engid )
<engid> - valid for 'push' only
allocdump
Usage: dbcc allocdump( Database Id/Name, Alloc_Page_Id [, Partition Id [, printopt=[1]{ 1 | 0 } ] ] )
Displays all extents of an allocation page.
alterws
Usage: dbcc alterws({dbccdb|dbccalt}, workspace name, {scan|text}, size)
anchors
Usage: dbcc anchors ( [dbid | dbname] )
astc
Usage: dbcc astc [(cmdname={prdtxp|crtcoord} [, dtxp id={-1,0,1,2...} [, mode={0,1} [, xact key ]]])]
bhash
Usage: dbcc bhash( cache_name | cache_id [, clet_id [, { print_bufs | no_print }, bucket_limit]] )
bufcount
Usage: dbcc bufcount [ ( num_chains [, cache_name | cache_id [, cachelet_id ] ) ]
buffer
Usage: dbcc buffer [ (dbid | dbname [, objid | objname [, indid [, ptnid, [, nbufs [, printopt = { 0 | 1 | 2 } [, buftype = { kept | hashed | nothashed | ioerr} [, cache_name | cache_id [, cachelet_id ] ] ] ] ] ] ] ]) ]
Prints buffer headers and pages from the buffer cache.
bytes
Usage: dbcc bytes(startaddress, length [, showlist | STRUCT_NAME ])
If showlist option is specified, startaddress and length arguments are
unused. If a structure name is provided, length argument can be used
to specify special print options. To pick system selected default
print option, specify -1 to length argument.
cachedataremove
Usage: dbcc cachedataremove(dbid|dbname, objid|objname, partitionid/partitionname, indid|indexname)
Removes object's data from cache
cacheremove
Usage: dbcc cacheremove(dbid|dbname, objid|objname)
Uninstall and Uncache descriptor for an object from cache
checkcatalog
Usage: dbcc checkcatalog[( dbname [, fix | "all" | fixall ] ) ]
checkindex
Usage: dbcc checkindex( tablename | tabid, indexid [, bottom_up | NULL [, "partition_name" | partition_id] ] )
checktable
Usage: dbcc checktable( tablename | tabid [, skip_ncindex | "check spacebits" | bottom_up | NULL [, "partition_name" | partition_id | NULL [, dbid] ] ] )
checkalloc
Usage: dbcc checkalloc[ ( dbname [, fix | nofix ] ) ]
checkcatalog
Usage: dbcc checkcatalog[( dbname [, fix | "all" | fixall ] ) ]
checkdb
Usage: dbcc checkdb[( dbname [, skip_ncindex | "check spacebits" ] ) ]
checkdeviceinfo
Usage: dbcc checkindex( tablename | tabid, indexid [, bottom_up | NULL [, "partition_name" | partition_id] ] )
checkmdcache
Usage: dbcc checkmdcache( [ dbid | dbname [, objid={0 | 1 | 2 |...} [, verbose={0|1} [, lock={0|1} ] ] ] ])
checkobjcache
Usage: dbcc checkobjcache(dbid, objid [, indid [, ptnid] ] )
checkdebrepair
Usage: dbcc checkrepair( dbname , object [, option])
checkstorage
Usage: dbcc checkstorage( dbname )
checktable
Usage: dbcc checktable( tablename | tabid [, skip_ncindex | "check spacebits" | bottom_up | NULL [, "partition_name" | partition_id | NULL [, dbid] ] ] )
checkverify
Usage: dbcc checkverify( dbname [, tablename] [, exclusion_mode] )
chgdbname
Usage: dbcc chgindcachedvalue( tablename, indid, param_type, new_value )
chgmaxrowsperpg
chgmaxrowsperpg command has been obsoleted. Use the command chgindcachedvalue instead.
chgobjname
chgobjschema
cis
Usage: dbcc cis ("subcommand"[, vararg1, vararg2...])
Single entry point for all CIS (Component Integration Services) related dbcc commands.
Valid subcommands:
•remcon: displays a list of all remote connections made by all Component Integration Services clients. It takes no arguments.
•rusage: returns a report describing the total memory used by each Component Integration Services resource utilizing shared memory. The report describes total configured items, number of items used, number of items available, and total memory used for each resource. The CIS dbcc command dbcc cis("rusage") has been replaced by the Adaptive Server Enterprise command: dbcc mempools
•srvdes: returns a formatted list of all in-memory SRVDES structures, if no argument is provided. If an argument is provided, this command syncs the in-memory version of a SRVDES with information found in sysservers. dbcc cis("srvdes",[srvid])
•showcaps: shows a list of all capabilities for servername by capability name, ID, and value. dbcc cis("showcaps", "servername")
complete_xact
Usage: dbcc complete_xact("xid","commit|rollback" [, "1pc"])
Heuristically complete the given transaction.
createws
Usage: dbcc createws({dbccdb|dbccalt}, segment name, workspace name, {scan|text}, size)
dbcacheremove
Usage: dbcc dbcacheremove( dbid | dbname )
Uninstall and uncache database descriptor
dbinfo
Usage: dbcc dbinfo( [ dbname ] )
dbreboot
Usage: dbcc dbreboot(report | reboot | reboot_norecovery | "shutdown" | shutdown_load | restart | restart_norecovery, <dbname1> [, <dbname2> ...])
Reboot the specified databases.
dbrepair
Usage: dbcc dbrepair( dbname, option = { dropdb | fixindex | fixsysindex }, table, indexid )
dbtable
Usage: dbcc dbtable[(dbid|dbname)] !delbuf Usage: ~np~ dbcc delbuff ( dbid | dbname, pageid [, flushdirty = { 0 | 1 | 2} [, lock = {0 | 1 | 2} [, cache_name | cache_id ] ] ] )
delete_dol_datapage
Usage: dbcc delete_dol_datapage(dbid, objid, pageno [, "noblock" ] )
Attempts to deallocate a dol datapage.
delete_row
Usage: dbcc delete_row( dbid|dbname, pageid, delete_by_row = { 1 | 0 }, rownum [, rid_position)
deleteplan
Usage: dbcc deleteplan(dbid | name, objid | name, plan_number | "all")
plan_number: delete a particular plan indicated by plan_number
all: delete all plans associated with this procedure object
des [ ( dbid | dbname [, objid | objname [, mode = { 0 | 1 } 1 } ] ] ) ] ~/np~
des
Usage: des [ ( dbid | dbname [, objid | objname [, mode = { 0 | 1 } [, print option = { 0 | 1 }] ] ] ) ]
Displays the contents of the object DES (descriptor)
diskmap
Usage: dbcc diskmap ("show" | "list" | "test" | "check" [,dbid | dbname] [,option])
Show, list, test or check the disk maps for the database <dbid>/<dbname>.
drop_haproxydbs
dropextendedproc
dynmp (????)
dynmp( option = { crt_ptab }
extengine
Usage: dbcc extengine( srvname, extengine_class = {10}, command = {start|stop|status|START|STOP|STATUS} )
extentcheck
Usage: dbcc extentcheck( dbid | dbname, objid | objname, indexid | indexname, sort = {0|1} [, ptnid | ptnname] )
extentdump
Usage: dbcc extentdump( dbid | dbname, page )
extentzap
Usage: dbcc extentzap( dbid | dbname, objid | objname, indexid | indexname, sort = {0|1} [, ptnid | ptnname] )
findnotfullextents
Usage: dbcc findnotfullextents( dbid | dbname, objid | objname, indexid | indexname, sort = {0|1} [, ptnid | ptnname] )
fix_al
Usage: dbcc fix_al( [ dbname ] )
fix_page
Usage: fix_page( dbid | dbname, objid | name, pageid, fix_type = { timestamp | 0 } )
Attempts to fix a page timestamp fault.
flushstats
Usage: dbcc flushstats(dbid|dbname, objid|objname, indid)
Copies statistics for a table and index, if specified from its
in-memory descriptors to corresponding rows in systabstats
forget_xact
Usage: dbcc forget_xact("xid")
Forget a heuristically completed transaction.
gam
Usage: dbcc gam( dbid | dbname, start_page, number_of_pages, 'check')
help
Usage: dbcc help( dbcc_command )
housekeeper
Usage: dbcc housekeeper ( 'pr_gcq' | 'pr_tsq' | 'pr_asq', [<engid>] )
Valid values for <command> are:
pr_gcq - Print the garbage collection queue.
pr_tsq - Print the table statistics queue.
pr_asq - Print the account statistics queue.
ides
Usage: dbcc ides( dbid | dbname, objid, [indid={-1,0,1...255} [, printopt={0|1|2|3|4|5} [, mode={0|1} ] ] ] )
Displays the contents of the index DES (descriptor)
ind
Usage: dbcc ind( dbid, objid, printopt = { 0 | 1 | 2 } )
indexalloc
Usage: dbcc indexalloc(tablename | tabid, indid [, optimized | fast [, fix | nofix [, partition_name | partition_id ]]])
listoam
Usage: dbcc listoam(dbid | dbname, objid | objname, indid | indname [, ptnid | ptnname [, cache=[1]{ 1 | 'cache' | 0 | 'disk' } [, printopt=[1]{ 1 | 'verbose' | 0 | 'terse' } ] ] ])
locateindexpgs
Usage: dbcc locateindexpgs( dbid|dbname, objid|objname, page, indexid|indexname, level[, index_partition_id|index_partition_name | 0] )
lock_release
Usage: dbcc lock_release( spid, { "table", "page", "row" }, locktype = { "ex_tab", "sh_page", "up_row", etc. }, dbid, objid [, pageno [, rowno ] ] [, "force" ] )
Attempts to release the lock held by the given spid that was badly terminated.
lockrequeue
Usage: dbcc lock_requeue( locktype = { "table", "page", "row" }, dbid, objid [, pageno [, rowno ] ] )
lockunit
log
Usage: dbcc log( [dbid | dbname] [,objid | objname] [,page] [,row] [,nrecords] [,type={ -1..84 }] ,printopt={ 0 | 1 | 2 | 3 } [,indid | indname] [,ptnid | ptnname] )
memdump
Usage: dbcc memdump(<an optional string>)
Writes a shared memory dump.
to configure:
sp_configure 'dump on conditions', 1
sp_shmdumpconfig "add", "dbcc"
Pages, buffers and proc cache may be omitted by default.
For a full dump:
sp_shmdumpconfig "add", "dbcc", null, null, null, null, include_page, include_proc, include_unused
Please see sp_shmdumpconfig documentation for more options.
msg
Usage: dbcc netmemshow( option = {1 | 2 | 3} )
netmemusage
help
Usage: dbcc help(dbcc command) !object_atts !object_stats Usage: ~np~ dbcc object_stats( locks, dbid, objid, spid )
page
Usage: dbcc page( dbid|dbname, pagenum [, printopt={0|1|2|3|4} [, cache={0|1} [, logical={1|0} [, cachename | -1 ]]]] )
pglinkage
Usage: dbcc pglinkage( dbid|dbname, start, number, printopt=[0]{ 0 | 1 | 2 | 3 }, target, order=[1]{ 1 | 'ascending' | 0 | 'descending' } )
pktmemshow
Usage: dbcc pktmemshow( option = {spid} )
pravailabletempdbs
Usage: dbcc pravailabletempdbs
Prints the global list of available temporary databases.
printindextree
Usage: dbcc printindextree( dbid|dbname, pagenum [, printopt={0} [, cache={0|1} [, logical={1|0} [, cachename | -1 ]]]] )
proc_cache
Usage: dbcc proc_cache (free_unused | flush_elc)
- free_unused: free unused procedures from cache
- flush_elc: flush pages from Engine Local Cache.
procbuf
Usage: dbcc procbuf( dbid, objid, nbufs, printopt = { 0 | 1 } )
prtipage
Usage: dbcc prtipage( dbid|dbname, objid|objname, indexid|indexname, indexpage )
prxdeshash
Usage: dbcc prxdeshash
Prints and checks the consistency of the transaction hash table.
pss
Usage: dbcc pss [( suid [, spid [, printopt = {0|1|2|3|4|5} [, getspinlock = {0|1}] ] ] ) ]
rebuild_text
Usage: dbcc rebuild_text( table | "all" [, column [, text_page [, data_partition ] ] ] )
Rebuild internal structure use by text/image datatypes.
'table' - object id or object name.
"all" specified for table will rebuild the L0-cache and TIPSA for all text/image objects in the database.
'column' - column name or column id.
'text_page' is the First Text Page number of the text/image data value.
'data_partition' - data partition id or data partition name.
rebuildextents
Usage: dbcc rebuildextents( dbid | dbname, objid | objname, indexid | indexname [, ptnid | ptnname] )
recachelimits
Usage: dbcc recachelimits
Updates the in-core structures representing sysresourcelimits. Should be called after making changes to master.dbo.sysresourcelimits.
refreshides
Usage: dbcc refreshides( dbid | dbname, objid, indid, field )
called after updating a field of a row in sysindexes to update the in-core structure representing this sysindexes row as the sysindexes rows cache is not a write thru cache.
refreshpdes
Usage: dbcc refreshpdes( dbid | dbname, objid, indid, ptnid, field )
called after updating a field of a row in syspartitions to update the in-core structure representing this syspartitions row as the syspartitions rows cache is not a write thru cache.
reindex
Usage: dbcc reindex( tablename, reindexflags )
1
Do not force single user mode for rebuild of system table's index.
2
Skip rebuild of APL clustered index.
4
Do not raise exception during checktable.
8
Do not print dbcc complete message.
16
Force rebuild of indexes including APL clustered idx.
reset_etr
Usage: dbcc reset_etr(spid)
Reset the 'external transaction required' attribute in the target process
resource
sdes
Usage: dbcc sdes [(spid [, printopt = {0|1|2|3}])]
Displays a spid DES (descriptor).
show_bucket
Usage: dbcc show_bucket( dbid | dbname, pageid, lookup_type = { 1 | 2 } [, cache_name | cache_id | -1 ] )
show_translation
Usage: dbcc show_translation [(dbid | dbname)]
Shows which tables are being translated in the current database, or the specified database.
showrecovery
Usage: dbcc showrecovery [(dbid | dbname)]
Show recovery diagnostics/statistics for a specified database <dbid>/<dbname> or all databases.
site
Usage: dbcc site [( site id = { all | 0 | site id } [, logconns = { 0 | 1 } [, lock = { 0 | 1 } ] ] )]
socktab
Usage: dbcc socktab [(<virtual socket number> | <kpid>)] (Available only for TCP/IP) !spfreedll !sqltext !stacktrace !tab __Usage:__ ~np~ dbcc tab( dbid, objid, printopt = { 0 | 1 | 2 } )
tablealloc
Usage: dbcc tablealloc(tablename | tabid [, full | optimized | fast [, fix | nofix [, data_partition_name | data_partition_id ]]])
textalloc
Usage: dbcc textalloc("ALL"|tablename|tabid, [full | optimized | fast],[fix | nofix])
thread
Usage: dbcc thread(cmd, 'spid' | 'all' | 'proc_type, proc_type,...', spid# | 'flag, flag,...')
Command:
stop
resume
summary
Process type:
astc
audit
ckpt
deadlock
dtc
hbc
hk
java
job
license
log
mirror
net
plc
port
quiesce
recovery
rep
rtds
shutdown
site
Flags:
all
user
system
myself
remote
newconn
consaonly
conisqlonly
holdnew
thresholds
Usage: dbcc thresholds( dbid | dbname )
Displays the thresholds set for the given database
traceflags
Usage: dbcc traceflags [( printopt = { 1 | 2 } )]
traceoff
Usage: dbcc traceoff( tracenum [, tracenum ... ] )
traceon
Usage: dbcc traceon( tracenum [, tracenum ... ] )
traceprint
translate_fileapi
Usage: dbcc translate_fileapi ("oem" | "ansi" )
Set the file name translation mode on the Windows operating system
tune
Usage: dbcc tune(cpuaffinity, start_cpu [, on | off])
update_tmode
upgrade_object
Usage: dbcc upgrade_object( [dbid | dbname [, obj_name | obj_type [, force | check ]]] )
usedextents
Usage: dbcc usedextents( dbid|dbname, type = {0|1|2|all|log|nonlog}, display_opts = {0|1|display|count} [, bypiece = {0|1} [, printopt = {0,1}]] )
user_stats
Usage: dbcc user_stats( locks, dbid, objid, spid )
xdes
Usage: dbcc xdes [("<spid>")]
Prints transaction descriptor information of the given spid.
xdeshash
Usage: dbcc xdeshash
Checks the consistency of the transaction hash table.
xdespool
Usage: dbcc xdespool
Checks the consistency of the global XDES (transaction descriptor) pool.
xls
This will require a separate page, TODO
dbcc tune(cpuaffinity, start_cpu [, on | off])
** please check these commands before use on prod box
====================================================================
===============================================================
addtempdb
Usage: dbcc addtempdb( dbid|dbname )
Adds a temporary database to the global list of available temporary databases. If the database does not exist or is not a temporary database, an error is generated. If the database is already a member of the list, an informational message prints.
affinity
Usage: dbcc affinity( 'push' | 'pop', engid )
<engid> - valid for 'push' only
allocdump
Usage: dbcc allocdump( Database Id/Name, Alloc_Page_Id [, Partition Id [, printopt=[1]{ 1 | 0 } ] ] )
Displays all extents of an allocation page.
alterws
Usage: dbcc alterws({dbccdb|dbccalt}, workspace name, {scan|text}, size)
anchors
Usage: dbcc anchors ( [dbid | dbname] )
astc
Usage: dbcc astc [(cmdname={prdtxp|crtcoord} [, dtxp id={-1,0,1,2...} [, mode={0,1} [, xact key ]]])]
bhash
Usage: dbcc bhash( cache_name | cache_id [, clet_id [, { print_bufs | no_print }, bucket_limit]] )
bufcount
Usage: dbcc bufcount [ ( num_chains [, cache_name | cache_id [, cachelet_id ] ) ]
buffer
Usage: dbcc buffer [ (dbid | dbname [, objid | objname [, indid [, ptnid, [, nbufs [, printopt = { 0 | 1 | 2 } [, buftype = { kept | hashed | nothashed | ioerr} [, cache_name | cache_id [, cachelet_id ] ] ] ] ] ] ] ]) ]
Prints buffer headers and pages from the buffer cache.
bytes
Usage: dbcc bytes(startaddress, length [, showlist | STRUCT_NAME ])
If showlist option is specified, startaddress and length arguments are
unused. If a structure name is provided, length argument can be used
to specify special print options. To pick system selected default
print option, specify -1 to length argument.
cachedataremove
Usage: dbcc cachedataremove(dbid|dbname, objid|objname, partitionid/partitionname, indid|indexname)
Removes object's data from cache
cacheremove
Usage: dbcc cacheremove(dbid|dbname, objid|objname)
Uninstall and Uncache descriptor for an object from cache
checkcatalog
Usage: dbcc checkcatalog[( dbname [, fix | "all" | fixall ] ) ]
checkindex
Usage: dbcc checkindex( tablename | tabid, indexid [, bottom_up | NULL [, "partition_name" | partition_id] ] )
checktable
Usage: dbcc checktable( tablename | tabid [, skip_ncindex | "check spacebits" | bottom_up | NULL [, "partition_name" | partition_id | NULL [, dbid] ] ] )
checkalloc
Usage: dbcc checkalloc[ ( dbname [, fix | nofix ] ) ]
checkcatalog
Usage: dbcc checkcatalog[( dbname [, fix | "all" | fixall ] ) ]
checkdb
Usage: dbcc checkdb[( dbname [, skip_ncindex | "check spacebits" ] ) ]
checkdeviceinfo
Usage: dbcc checkindex( tablename | tabid, indexid [, bottom_up | NULL [, "partition_name" | partition_id] ] )
checkmdcache
Usage: dbcc checkmdcache( [ dbid | dbname [, objid={0 | 1 | 2 |...} [, verbose={0|1} [, lock={0|1} ] ] ] ])
checkobjcache
Usage: dbcc checkobjcache(dbid, objid [, indid [, ptnid] ] )
checkdebrepair
Usage: dbcc checkrepair( dbname , object [, option])
checkstorage
Usage: dbcc checkstorage( dbname )
checktable
Usage: dbcc checktable( tablename | tabid [, skip_ncindex | "check spacebits" | bottom_up | NULL [, "partition_name" | partition_id | NULL [, dbid] ] ] )
checkverify
Usage: dbcc checkverify( dbname [, tablename] [, exclusion_mode] )
chgdbname
Usage: dbcc chgindcachedvalue( tablename, indid, param_type, new_value )
chgmaxrowsperpg
chgmaxrowsperpg command has been obsoleted. Use the command chgindcachedvalue instead.
chgobjname
chgobjschema
cis
Usage: dbcc cis ("subcommand"[, vararg1, vararg2...])
Single entry point for all CIS (Component Integration Services) related dbcc commands.
Valid subcommands:
•remcon: displays a list of all remote connections made by all Component Integration Services clients. It takes no arguments.
•rusage: returns a report describing the total memory used by each Component Integration Services resource utilizing shared memory. The report describes total configured items, number of items used, number of items available, and total memory used for each resource. The CIS dbcc command dbcc cis("rusage") has been replaced by the Adaptive Server Enterprise command: dbcc mempools
•srvdes: returns a formatted list of all in-memory SRVDES structures, if no argument is provided. If an argument is provided, this command syncs the in-memory version of a SRVDES with information found in sysservers. dbcc cis("srvdes",[srvid])
•showcaps: shows a list of all capabilities for servername by capability name, ID, and value. dbcc cis("showcaps", "servername")
complete_xact
Usage: dbcc complete_xact("xid","commit|rollback" [, "1pc"])
Heuristically complete the given transaction.
createws
Usage: dbcc createws({dbccdb|dbccalt}, segment name, workspace name, {scan|text}, size)
dbcacheremove
Usage: dbcc dbcacheremove( dbid | dbname )
Uninstall and uncache database descriptor
dbinfo
Usage: dbcc dbinfo( [ dbname ] )
dbreboot
Usage: dbcc dbreboot(report | reboot | reboot_norecovery | "shutdown" | shutdown_load | restart | restart_norecovery, <dbname1> [, <dbname2> ...])
Reboot the specified databases.
dbrepair
Usage: dbcc dbrepair( dbname, option = { dropdb | fixindex | fixsysindex }, table, indexid )
dbtable
Usage: dbcc dbtable[(dbid|dbname)] !delbuf Usage: ~np~ dbcc delbuff ( dbid | dbname, pageid [, flushdirty = { 0 | 1 | 2} [, lock = {0 | 1 | 2} [, cache_name | cache_id ] ] ] )
delete_dol_datapage
Usage: dbcc delete_dol_datapage(dbid, objid, pageno [, "noblock" ] )
Attempts to deallocate a dol datapage.
delete_row
Usage: dbcc delete_row( dbid|dbname, pageid, delete_by_row = { 1 | 0 }, rownum [, rid_position)
deleteplan
Usage: dbcc deleteplan(dbid | name, objid | name, plan_number | "all")
plan_number: delete a particular plan indicated by plan_number
all: delete all plans associated with this procedure object
des [ ( dbid | dbname [, objid | objname [, mode = { 0 | 1 } 1 } ] ] ) ] ~/np~
des
Usage: des [ ( dbid | dbname [, objid | objname [, mode = { 0 | 1 } [, print option = { 0 | 1 }] ] ] ) ]
Displays the contents of the object DES (descriptor)
diskmap
Usage: dbcc diskmap ("show" | "list" | "test" | "check" [,dbid | dbname] [,option])
Show, list, test or check the disk maps for the database <dbid>/<dbname>.
drop_haproxydbs
dropextendedproc
dynmp (????)
dynmp( option = { crt_ptab }
extengine
Usage: dbcc extengine( srvname, extengine_class = {10}, command = {start|stop|status|START|STOP|STATUS} )
extentcheck
Usage: dbcc extentcheck( dbid | dbname, objid | objname, indexid | indexname, sort = {0|1} [, ptnid | ptnname] )
extentdump
Usage: dbcc extentdump( dbid | dbname, page )
extentzap
Usage: dbcc extentzap( dbid | dbname, objid | objname, indexid | indexname, sort = {0|1} [, ptnid | ptnname] )
findnotfullextents
Usage: dbcc findnotfullextents( dbid | dbname, objid | objname, indexid | indexname, sort = {0|1} [, ptnid | ptnname] )
fix_al
Usage: dbcc fix_al( [ dbname ] )
fix_page
Usage: fix_page( dbid | dbname, objid | name, pageid, fix_type = { timestamp | 0 } )
Attempts to fix a page timestamp fault.
flushstats
Usage: dbcc flushstats(dbid|dbname, objid|objname, indid)
Copies statistics for a table and index, if specified from its
in-memory descriptors to corresponding rows in systabstats
forget_xact
Usage: dbcc forget_xact("xid")
Forget a heuristically completed transaction.
gam
Usage: dbcc gam( dbid | dbname, start_page, number_of_pages, 'check')
help
Usage: dbcc help( dbcc_command )
housekeeper
Usage: dbcc housekeeper ( 'pr_gcq' | 'pr_tsq' | 'pr_asq', [<engid>] )
Valid values for <command> are:
pr_gcq - Print the garbage collection queue.
pr_tsq - Print the table statistics queue.
pr_asq - Print the account statistics queue.
ides
Usage: dbcc ides( dbid | dbname, objid, [indid={-1,0,1...255} [, printopt={0|1|2|3|4|5} [, mode={0|1} ] ] ] )
Displays the contents of the index DES (descriptor)
ind
Usage: dbcc ind( dbid, objid, printopt = { 0 | 1 | 2 } )
indexalloc
Usage: dbcc indexalloc(tablename | tabid, indid [, optimized | fast [, fix | nofix [, partition_name | partition_id ]]])
listoam
Usage: dbcc listoam(dbid | dbname, objid | objname, indid | indname [, ptnid | ptnname [, cache=[1]{ 1 | 'cache' | 0 | 'disk' } [, printopt=[1]{ 1 | 'verbose' | 0 | 'terse' } ] ] ])
locateindexpgs
Usage: dbcc locateindexpgs( dbid|dbname, objid|objname, page, indexid|indexname, level[, index_partition_id|index_partition_name | 0] )
lock_release
Usage: dbcc lock_release( spid, { "table", "page", "row" }, locktype = { "ex_tab", "sh_page", "up_row", etc. }, dbid, objid [, pageno [, rowno ] ] [, "force" ] )
Attempts to release the lock held by the given spid that was badly terminated.
lockrequeue
Usage: dbcc lock_requeue( locktype = { "table", "page", "row" }, dbid, objid [, pageno [, rowno ] ] )
lockunit
log
Usage: dbcc log( [dbid | dbname] [,objid | objname] [,page] [,row] [,nrecords] [,type={ -1..84 }] ,printopt={ 0 | 1 | 2 | 3 } [,indid | indname] [,ptnid | ptnname] )
memdump
Usage: dbcc memdump(<an optional string>)
Writes a shared memory dump.
to configure:
sp_configure 'dump on conditions', 1
sp_shmdumpconfig "add", "dbcc"
Pages, buffers and proc cache may be omitted by default.
For a full dump:
sp_shmdumpconfig "add", "dbcc", null, null, null, null, include_page, include_proc, include_unused
Please see sp_shmdumpconfig documentation for more options.
msg
Usage: dbcc netmemshow( option = {1 | 2 | 3} )
netmemusage
help
Usage: dbcc help(dbcc command) !object_atts !object_stats Usage: ~np~ dbcc object_stats( locks, dbid, objid, spid )
page
Usage: dbcc page( dbid|dbname, pagenum [, printopt={0|1|2|3|4} [, cache={0|1} [, logical={1|0} [, cachename | -1 ]]]] )
pglinkage
Usage: dbcc pglinkage( dbid|dbname, start, number, printopt=[0]{ 0 | 1 | 2 | 3 }, target, order=[1]{ 1 | 'ascending' | 0 | 'descending' } )
pktmemshow
Usage: dbcc pktmemshow( option = {spid} )
pravailabletempdbs
Usage: dbcc pravailabletempdbs
Prints the global list of available temporary databases.
printindextree
Usage: dbcc printindextree( dbid|dbname, pagenum [, printopt={0} [, cache={0|1} [, logical={1|0} [, cachename | -1 ]]]] )
proc_cache
Usage: dbcc proc_cache (free_unused | flush_elc)
- free_unused: free unused procedures from cache
- flush_elc: flush pages from Engine Local Cache.
procbuf
Usage: dbcc procbuf( dbid, objid, nbufs, printopt = { 0 | 1 } )
prtipage
Usage: dbcc prtipage( dbid|dbname, objid|objname, indexid|indexname, indexpage )
prxdeshash
Usage: dbcc prxdeshash
Prints and checks the consistency of the transaction hash table.
pss
Usage: dbcc pss [( suid [, spid [, printopt = {0|1|2|3|4|5} [, getspinlock = {0|1}] ] ] ) ]
rebuild_text
Usage: dbcc rebuild_text( table | "all" [, column [, text_page [, data_partition ] ] ] )
Rebuild internal structure use by text/image datatypes.
'table' - object id or object name.
"all" specified for table will rebuild the L0-cache and TIPSA for all text/image objects in the database.
'column' - column name or column id.
'text_page' is the First Text Page number of the text/image data value.
'data_partition' - data partition id or data partition name.
rebuildextents
Usage: dbcc rebuildextents( dbid | dbname, objid | objname, indexid | indexname [, ptnid | ptnname] )
recachelimits
Usage: dbcc recachelimits
Updates the in-core structures representing sysresourcelimits. Should be called after making changes to master.dbo.sysresourcelimits.
refreshides
Usage: dbcc refreshides( dbid | dbname, objid, indid, field )
called after updating a field of a row in sysindexes to update the in-core structure representing this sysindexes row as the sysindexes rows cache is not a write thru cache.
refreshpdes
Usage: dbcc refreshpdes( dbid | dbname, objid, indid, ptnid, field )
called after updating a field of a row in syspartitions to update the in-core structure representing this syspartitions row as the syspartitions rows cache is not a write thru cache.
reindex
Usage: dbcc reindex( tablename, reindexflags )
1
Do not force single user mode for rebuild of system table's index.
2
Skip rebuild of APL clustered index.
4
Do not raise exception during checktable.
8
Do not print dbcc complete message.
16
Force rebuild of indexes including APL clustered idx.
reset_etr
Usage: dbcc reset_etr(spid)
Reset the 'external transaction required' attribute in the target process
resource
sdes
Usage: dbcc sdes [(spid [, printopt = {0|1|2|3}])]
Displays a spid DES (descriptor).
show_bucket
Usage: dbcc show_bucket( dbid | dbname, pageid, lookup_type = { 1 | 2 } [, cache_name | cache_id | -1 ] )
show_translation
Usage: dbcc show_translation [(dbid | dbname)]
Shows which tables are being translated in the current database, or the specified database.
showrecovery
Usage: dbcc showrecovery [(dbid | dbname)]
Show recovery diagnostics/statistics for a specified database <dbid>/<dbname> or all databases.
site
Usage: dbcc site [( site id = { all | 0 | site id } [, logconns = { 0 | 1 } [, lock = { 0 | 1 } ] ] )]
socktab
Usage: dbcc socktab [(<virtual socket number> | <kpid>)] (Available only for TCP/IP) !spfreedll !sqltext !stacktrace !tab __Usage:__ ~np~ dbcc tab( dbid, objid, printopt = { 0 | 1 | 2 } )
tablealloc
Usage: dbcc tablealloc(tablename | tabid [, full | optimized | fast [, fix | nofix [, data_partition_name | data_partition_id ]]])
textalloc
Usage: dbcc textalloc("ALL"|tablename|tabid, [full | optimized | fast],[fix | nofix])
thread
Usage: dbcc thread(cmd, 'spid' | 'all' | 'proc_type, proc_type,...', spid# | 'flag, flag,...')
Command:
stop
resume
summary
Process type:
astc
audit
ckpt
deadlock
dtc
hbc
hk
java
job
license
log
mirror
net
plc
port
quiesce
recovery
rep
rtds
shutdown
site
Flags:
all
user
system
myself
remote
newconn
consaonly
conisqlonly
holdnew
thresholds
Usage: dbcc thresholds( dbid | dbname )
Displays the thresholds set for the given database
traceflags
Usage: dbcc traceflags [( printopt = { 1 | 2 } )]
traceoff
Usage: dbcc traceoff( tracenum [, tracenum ... ] )
traceon
Usage: dbcc traceon( tracenum [, tracenum ... ] )
traceprint
translate_fileapi
Usage: dbcc translate_fileapi ("oem" | "ansi" )
Set the file name translation mode on the Windows operating system
tune
Usage: dbcc tune(cpuaffinity, start_cpu [, on | off])
update_tmode
upgrade_object
Usage: dbcc upgrade_object( [dbid | dbname [, obj_name | obj_type [, force | check ]]] )
usedextents
Usage: dbcc usedextents( dbid|dbname, type = {0|1|2|all|log|nonlog}, display_opts = {0|1|display|count} [, bypiece = {0|1} [, printopt = {0,1}]] )
user_stats
Usage: dbcc user_stats( locks, dbid, objid, spid )
xdes
Usage: dbcc xdes [("<spid>")]
Prints transaction descriptor information of the given spid.
xdeshash
Usage: dbcc xdeshash
Checks the consistency of the transaction hash table.
xdespool
Usage: dbcc xdespool
Checks the consistency of the global XDES (transaction descriptor) pool.
xls
This will require a separate page, TODO
dbcc tune(cpuaffinity, start_cpu [, on | off])
** please check these commands before use on prod box
====================================================================
Comparison of checks performed by dbcc commands
|
|||||||
Checks performed
|
checkstorage
|
checktable
|
checkdb
|
checkalloc
|
indexalloc
|
tablealloc
|
checkcatalog
|
Checks allocation of text valued columns
|
X
|
||||||
Checks index consistency
|
X
|
X
|
|||||
Checks index sort order
|
X
|
X
|
|||||
Checks OAM page entries
|
X
|
X
|
X
|
X
|
X
|
||
Checks page allocation
|
X
|
X
|
X
|
X
|
|||
Checks page consistency
|
X
|
X
|
X
|
||||
Checks pointer consistency
|
X
|
X
|
X
|
||||
Checks system tables
|
X
|
||||||
Checks text column chains
|
X
|
X
|
X
|
X
|
|||
Checks text valued columns
|
X
|
X
|
X
|
||||
Comparison of the performance of dbcc commands
|
||||
Command
and option
|
Level
|
Locking
and performance
|
Speed
|
Thorough-ness
|
checkstorage
|
Page
chains and data rows for all indexes, allocation pages, OAM pages, device and
partition statistics
|
No
locking; performs extensive I/O and may saturate the system’s I/O; can use
dedicated cache with minimal impact on other caches
|
Fast
|
High
|
checktable
checkdb
|
Page
chains, sort order, data rows, and partition statistics for all indexes
|
Shared
table lock; dbcc checkdb locks one table at a time and releases
the lock after it finishes checking that table
|
Slow
|
High
|
checktable
checkdb with skip_ncindex
|
Page
chains, sort order, and data rows for tables and clustered indexes
|
Shared
table lock; dbcc checkdb locks one table at a time and releases
the lock after it finishes checking that table
|
Up
to 40 percent faster than without skip_ncindex
|
Medium
|
checkalloc
|
Page
chains and partition statistics
|
No
locking; performs extensive I/O and may saturate the I/O calls; only
allocation pages are cached
|
Slow
|
High
|
tablealloc
full indexalloc full with full
|
Page
chains
|
Shared
table lock; performs extensive I/O; only allocation pages are cached
|
Slow
|
High
|
tablealloc
indexalloc with optimized
|
Allocation
pages
|
Shared
table lock; performs extensive I/O; only allocation pages are cached
|
Moderate
|
Medium
|
tablealloc
indexalloc with fast
|
OAM
pages
|
Shared
table lock
|
Fast
|
Low
|
checkcatalog
|
Rows
in system tables
|
Shared
page locks on system catalogs; releases lock after each page is checked; very
few pages cached
|
Moderate
|
Medium
|
===============================================================
| Option | Impact |
|---|---|
| Trace Flag 3607 | Starts SQL Server without recovering any databases |
| Trace Flag 3608 | Starts SQL Server, recovering master only |
| User Database Status -32768 | Starts SQL Server without recovering the user database |