DBCC Commands

1.       DBCC INPUTBUFFER       Displays the last statement sent from a client to an instance of Microsoft SQL Server.

DBCC INPUTBUFFER ( session_id [ , request_id ])

DBCC INPUTBUFFER (52);

2.       DBCC OPENTRAN             helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database.

DBCC OPENTRAN

3.       DBCC PROCCACHE           Displays information in a table format about the procedure cache.

4.       DBCC SHOW_STATISTICS              displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan.

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);

5.       DBCC CHECKCONSTRAINTS          Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS(Table1)

6.       DBCC CHECKDB                 Checks the logical and physical integrity of all the objects in the specified database. Validates the contents of every indexed view in the database.

7.       DBCC CHECKIDENT          Checks the current identity value for the specified table in SQL Server 2012 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

DBCC CHECKIDENT ('Person.AddressType')

8.       DBCC CHECKTABLE          Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKTABLE ("HumanResources.Employee")

9.       DBCC CLEANTABLE          Reclaims space from dropped variable-length columns in tables or indexed views.

DBCC CLEANTABLE (AdventureWorks2012,"Production.Document", 0)

10.   DBCC DROPCLEANBUFFERS         Removes all clean buffers from the buffer pool.

11.   DBCC FREEPROCCACHE                 Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

12.   DBCC INDEXDEFRAG       Defragments indexes of the specified table or view.

DBCC INDEXDEFRAG (AdventureWorks2012, "Production.Product", PK_Product_ProductID)

13.   DBCC SHRINKDATABASE               Shrinks the size of the data and log files in the specified database.

DBCC SHRINKDATABASE (UserDB, 10);

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);