Database console commands or DBCC are T-SQL Commands grouped in to four categories, Maintenance, Miscellaneous, informational and validation. This blog lists down some frequently used DBCC commands.

A brief description of the DBCC command categories is given below.

Maintenance: Maintenance tasks on a database, index, or filegroup.

Miscellaneous: Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

Informational: Tasks that gather and display various types of information.

Validation: Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

List of frequently used DBCC Command

DBCC CHECKCATALOG

It is used to perform consistency check for a table/catalog for a particular database.

DBCC CHECKCONSTRAINTS

It is used to check integrity of a particular constraint or all constraints on a particular table for a database.

DBCC CHECKALLOC

It is used to check page usage and allocation of a database.

DBCC CHECKTABLE(tablename)

It is used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table.

DBCC SQLPERF(logspace)

Displays T-log size and space used % for all databases.

DBCC SHOWFILESTATS

Display Extent information for a database.

DBCC CHECKIDENT(tablename)

It is used to check identity information or to return current identity value for a particular table.

DBCC TRACEOFF

It is used to disable tracing

DBCC TRACEON

It is used to enable tracing.

DBCC TRACESTATUS

It displays the trace status.

DBCC USEROPTIONS

It displays the active SET options for a particular database

DBCC INPUTBUFFER(sessionid)

It is used to get the last statement sent by session to SQL server.

DBCC SHRINKDATABASE(databasename)

It is used to shrink data and log files for a particular database. It release the free space to OS.

DBCC SHRINKFILE(file_id)

It is used to shrink individual database files.

DBCC LOGINFO

It displays virtual log file information contained in a T-log file.

DBCC HELP(<dbcc command>)

It displays the syntax of a DBCC command. To know syntax of DBCC SQLPERF, execute DBCC HELP(SQLPERF)

DBCC MEMORYSTATUS

It displays SQL Server memory allocation status.

DBCC Cleantable

It is used to reclaim space from dropped variable length columns in tables/indexed views

DBCC Opentran

It displays information about oldest active transaction and oldest distributed and non-distributed replication transaction

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook