DBCC

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


====================================================================



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