DBCC Commands

Microsoft SQL Server Database Console Commands (DBCC) are used for checking database integrity; performing maintenance operations on databases, tables, indexes, and filegroups; and collecting and displaying information during troubleshooting issues.

The first DBCC command to become familiar with is the DBCC HELP command, which returns the syntax with all the options for any DBCC command. The following command returns the syntax for DBCC CHECKDB.

DBCC HELP ('CHECKDB');

Result:

dbcc CHECKDB 
(
  { ‘database_name' | database_id | 0 }
  [ , NOINDEX
  | { REPAIR_ALLOW_DATA_LOSS
  | REPAIR_FAST
  | REPAIR_REBUILD
  } ]
)
  [ WITH
    {
      [ ALL_ERRORMSGS ]
      [ , [ NO_INFOMSGS ] ]
      [ , [ TABLOCK ] ]
      [ , [ ESTIMATEONLY ] ]
      [ , [ PHYSICAL_ONLY ] ]
      [ , [ DATA_PURITY ] ]
      [ , [ EXTENDED_LOGICAL_CHECKS ] ]
    }
  ]
DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

The following command returns all DBCC commands for which help is available.

DBCC HELP ('?');

Result:

checkalloc

checkcatalog

checkconstraints

checkdb

checkfilegroup

checkident

checktable

cleantable

dbreindex

dropcleanbuffers

free

freeproccache

freesessioncache

freesystemcache

help

indexdefrag

inputbuffer

opentran

outputbuffer

pintable

proccache

show_statistics

showcontig

shrinkdatabase

shrinkfile

sqlperf

traceoff

traceon

tracestatus

unpintable

updateusage

useroptions
DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

All DBCC commands report their activity or ...

Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial