What is the DBCC command and why it is used?

0 votes
120 views
Shikhar Arora asked 06-Dec-2019 in Database by Shikhar Arora

What is the DBCC command and why it is used

1 Answer

0 votes
Rahul Roi answered 15-Dec-2020 by Rahul Roi

What is the DBCC command and why it is used?

The Database-Consistency-Checker(DBCC) will help us to check the consistency of the database, this is also known as database console commands.

These commands are divided into the below categories.
  • Maintenance
  • Miscellaneous
  • Informational
  • Validation 

DBCC HELP

This command falls under the Miscellaneous category.

Syntax

DBCC HELP ('?'); 

The Maintenance   

Into this category includes commands for the tasks related to database, index, and filegroup, as shown below.

  • CLEANTABLE
  • DBREINDEX
  • DROPCLEANBUFFERS
  • FREEPROCCACHE
  • INDEXDEFRAG,
  • SHRINKDATABASE
  • SHRINKFILE
  • UPDATEUSAGE

The Miscellaneous   

Into this category includes commands for the tasks related to trace flag or DDL memory management, as shown below.

  • HELP
  • FLUSHAUTHCACHE
  • TRACEOFF
  • FREESESSIONCACHE
  • TRACEON
  • FREESYSTEMCACHE
  • CLONEDATABASE  

The Informational

Into this category includes commands for tasks to collect or display different types of information, like below.

  • INPUTBUFFER
  • SHOWCONTIG
  • OPENTRAN
  • OUTPUTBUFFER
  • PROCCACHE
  • SHOW_STATISTICS
  • SQLPERF
  • TRACESTATUS
  • USEROPTIONS

The Validation

Inti this category includes commands for the tasks to perform some kind of validation actions on the database, index, tables, etc., which I would say are more useful in certain ways, like below.

  • CHECKALLOC
  • CHECKCATALOG
  • CHECKCONSTRAINTS
  • CHECKDB
  • CHECKFILEGROUP
  • CHECKIDENT
  • CHECKTABLE