O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required