SQL Server DBCC Commands

What is DBCC Commands?

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. DBCC was previously expanded as Database Consistency Checker. Database Consistency Checker Commands give details in form of statistics about the SQL Server. DBCC act as Database console commands for SQL Server to check database consistency.

SQL Server DBCC CommandsDatabase Console Command statements are grouped into the following categories.

1. Maintenance (Maintenance tasks on a database, index, or filegroup.)

DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

2. Miscellaneous (Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.)

DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON

3. Informational (Tasks that gather and display various types of information.)

DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

4. Validation (Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.)

DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB.

Note: DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

DBCC Internal Database Snapshot Usage

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

• DBCC CHECKALLOC
• DBCC CHECKDB
• DBCC CHECKCATALOG
• DBCC CHECKFILEGROUP
• DBCC CHECKTABLE

When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.


<<Click here to see all posts>>

Leave a Reply

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

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.