SQL Server DBCC Commands

Database Consistency Checker DBCC Commands are used to get valuable insight into what’s going on inside SQL Server system including checking logical and physical consistency of database structures and fixing problems.

Some facts about DBCC commands

  • There are documented and undocumented DBCC commands. Use of undocumented commands is discouraged for SQL Server non-experts.
  • DBCC commands vary from SQL Server release to release
  • DBCC commands take input parameters and return values.
  • All DBCC command parameters can accept both Unicode and DBCS literals

What DBCC can be used for

  • Checking the whole database consistency
  • Checking the integrity of database tables and their related indexes
  • Examining the integrity of database pages
  • Inspecting individual pages allocation to a database, tables and indexes
  • Rebuilding the indexes on any given table as they become corrupted, unoptimised or just out of date
  • Clearing database memory buffer and cache for testing
  • Truncating SQL Server Error Log
  • Checking current query optimisation statistics for a table or indexed view
  • Shrinking the size of the specified database, data or log file for the current database
  • Inspecting transaction log space usage statistics for all databases

DBCC Categories

DBCC are grouped in to 4 major categories.

  1. Maintenance commands. Used for maintenance tasks in SQL Server database, index, or filegroup eg  DBCC DBREINDEX , DBCC DBREPAIR etc. Those DBCC commands operate on an internal read-only database snapshot that the Database Engine creates. This prevents blocking and concurrency problems when these commands are executed.
  2. Miscellaneous commands. They are mainly used for enabling trace flags, row-level locking or removing DLL from memory eg DBCC ROWLOCK , DBCC TRACEOFF etc
  3. Informational Commands. Used for checking some information about the database, connection, transaction and other objects eg DBCC OPENTRAN , DBCC SHOWCONTIG etc
  4. Validation Commands.  To perform validation operations on database, tables and other database objects or allocation of database pages like DBCC CHECKALLOC, DBCC CHECKCATALOG etc

To learn about all the DBCC commands run following script in SSMS.

DBCC TRACEON (2520)
DBCC HELP (‘?’)
GO

Leave a Reply

Your email address will not be published. Required fields are marked *