dbcc

Database consistency checker (dbcc) checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality.

Certain dbcc commands apply only to shared-disk clusters. See the separately listed dbcc syntax for clusters.

Syntax

dbcc addtempdb (dbid |database_name)
dbcc checkalloc [(database_name[, fix | nofix])]
dbcc checkcatalog [(database_name[, fix])
dbcc checkdb [(database_name[, skip_ncindex])]
dbcc checkindex ({table_name | table_id}, index_id
	[, bottom_up[, partition_name | partition_id]])
dbcc checkstorage [(database_name)]
dbcc checktable (table_name | table_id 
	[, skip_ncindex | fix_spacebits | "check spacebits" | 
	bottom_up | NULL[, partition_name | partition_id)
dbcc checkverify (dbname[, tblname[, ignore_exclusions]])
dbcc complete_xact (xid, {["commit", “1pc”] | "rollback"})
dbcc dbrepair (database_name, dropdb, flushthreshold)
dbcc engine ({offline, [enginenum] | "online"})
dbcc fix_text ({table_name | table_id})
dbcc forget_xact (xid)
dbcc indexalloc (table_name | table_id, index_id 
	[, optimized | fast | NULL [, fix | nofix | NULL 
	[, partition_name | partition_id]]])
dbcc monitor (increment, <group name>)
dbcc monitor (decrement, <group name>)
dbcc monitor (reset, <group name>)
dbcc pravailabletempdbs
dbcc rebuild_text (table_name | table_id | "all"[, column[, text_page 
	[, data_partition_name | data_partition_id]]])
dbcc reindex ({table_name | table_id})
dbcc serverlimits
dbcc stackused
dbcc tablealloc (table_name | table_id [, full | optimized | fast | NULL 
	[, fix | nofix | NULL [, data_partition_name | data_partition_id]]])

dbcc textalloc (table_name | table_id [, full | optimized | fast | NULL 
	[, fix | nofix | NULL [, data_partition_name | data_partition_id]]])
dbcc {traceon | traceoff} (flag [, flag ...])
dbcc tune ({ascinserts, {0 | 1} , table_name |
		cleanup, {0 | 1} | 
		cpuaffinity, start_cpu {, on| off} | 
		des_greedyalloc, dbid, object_name,
			" {on | off}" | deviochar vdevno, "batch_size" | 
		des_bind, dbid, object_name
		des_unbind, dbid, object_name
		doneinproc {0 | 1}})
dbcc upgrade_object [ ( dbid | dbname 
	[,[database.[owner].]compiled_object_name' | 
		 'check' | 'default' | 'procedure' | 'rule' | 
		 'trigger' | 'view' 
		 [, 'force' ] ] )
dbcc zapdefraginfo 
dbcc syntax for clusters only:
dbcc nodetraceon(trace_flag_number)
dbcc nodetraceoff(trace_flag_number)
dbcc set_scope_in_cluster("cluster"|"instance"|"scope")
dbcc quorum

Parameters

Examples

Usage

(Cluster Edition only) dbcc traceon and dbcc traceoff apply trace flags for the entire cluster, while dbcc nodetraceoff and dbcc nodetraceon apply trace flags locally.

See also sp_configure, sp_helpdb in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for dbcc commands differ based on your granular permissions settings. This table shows the permission requirement for each dbcc command.

Note: You can run a dbcc command if you have any one of the requirements (privileges or ownership) listed in the table for that command.
Permissions Requirement for dbcc Command

DBCC Command Name

Permission Requirement

Granular Permissions Disabled

Granular Permissions Enabled

addtempdb

  • Database owner

  • sa_role

  • Database owner

  • own database

checkalloc

  • dbcc checkalloc

  • Database owner

  • sa_role

  • dbcc checkalloc

checkcatalog

  • dbcc checkcatalog

  • Database owner

  • sa_role

  • dbcc checkcatalog

checkdb

  • dbcc checkdb

  • Database owner

  • sa_role

  • dbcc checkdb

checkindex

  • dbcc checkindex

  • Table owner

  • sa_role

  • dbcc checkindex

  • Table owner

checkstorage

  • dbcc checkstorage

  • Database owner

  • sa_role

  • dbcc checkstorage

checktable

  • dbcc checktable

  • Table owner

  • sa_role

  • dbcc checktable

  • Table owner

checkverify

  • dbcc checkverify

  • Database owner

  • sa_role

  • dbcc checkverify

cis showcaps

  • sa_role

  • manage server

cis remcon

  • sa_role

  • manage server

complete_xact

  • sa_role

  • manage server

dbrepair dropdb

  • Database owner

  • sa_role

  • Database owner

  • own database

engine

  • sa_role

  • manage server

fix_text

  • dbcc fix_text

  • Object owner

  • sa_role

  • dbcc fix_text

  • Object owner

forget_xact

  • sa_role

  • manage server

indexalloc

  • dbcc indexalloc

  • Table owner

  • sa_role

  • dbcc indexalloc

  • Table owner

monitor

  • sa_role

  • manage server

pravailabletempdbs

  • sa_role

  • manage server

quorum

  • sa_role

  • manage cluster

rebuild_text

  • Table owner

  • sa_role

  • Table owner

  • manage database

reindex

  • dbcc reindex

  • Table owner

  • sa_role

  • dbcc reindex

  • Table owner

serverlimits

  • sa_role

  • manager server

set_scope_in_cluster

  • sa_role

  • manage cluster

stackused

  • sa_role

  • manager server

tablealloc

  • dbcc tablealloc

  • Table owner

  • sa_role

  • dbcc tablealloc

  • Table owner

textalloc

  • dbcc textalloc

  • Table owner

  • sa_role

  • dbcc textalloc

  • Table owner

     trace flags – 3604, 3605

  • sa_role

  • set tracing

  • monitor qp performance

  • set switch

     all other trace flags

  • sa_role

  • set switch

tune ascinserts

  • dbcc tune

  • Table owner

  • sa_role

  • dbcc tune

  • Table owner

tune all other parameters

  • dbcc tune

  • sa_role

  • dbcc tune

upgrade_object

  • object owner

  • Database owner

  • sa_role

  • manage database

  • Object owner

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

81

Audit option

dbcc

Command or access audited

dbcc

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – Any of the dbcc keywords such as checkstorage and the options for that keyword

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
drop database