DBCC (Database console commands) in sql server

Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system. DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and troubleshooting exercises.

We can divide DBCC commands on below category

Ø  Maintenance

Ø  Informational

Ø  Validation

Ø  Miscellaneous

Maintenance DBCC Commands

Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file or index or file groups etc.

1. CLEANTABLE –DBCC CLEANTABLE reclaims space after a variable-length column is dropped. A variable-length column can be one of the following dateless: varchar,  nvarchar,  varchar(max),  nvarchar(max),  varbinary,  varbinary(max),  text,  ntext, image, sql_variant, and xml. The command does not reclaim space after a fixed-length column is dropped.

Syntax:

DBCC CLEANTABLE ‘Database Name’, ‘Table Name’, Batch (0))

DBCC CLEANTABLE is not supported for use on system tables, temporary tables, or the velocity memory optimized column store index portion of a table.

Best Practices

DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and we need to immediately reclaim the unused space. Alternatively, we can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation.

2. DBREINDEX – DBCC DBREINDEX can rebuild all the indexes for a table in one statement.

Syntax:

Use Database name DBCC DBREINDEX (‘table name’, ‘Index name’,80)

It will not work for system table and spatial indexes.

3. DROPCLEANBUFFERS – Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

DBCC DROPCLEANBUFFERS

4. 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.

Remove all elements from the plan cache for the entire instance

Use database name

DBCC FREEPROCCACHE

We can remove the particular execution plan

DBCC FREEPROCCACHE (plan_handle)

We can get the Plan using below sql script

SELECT cp.plan_handle, st.[text] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

5. INDEXDEFRAG – Defragments indexes of the specified table or view.

6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database

DBCC SHRINKDATABASE (‘Database Name‘, 10)

7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroups, allowing the file to be removed from the database.

USE Database name;

– Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (Database name, 1)

8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.

DBCC UPDATEUSAGE (Database name) Informational Commands

Informational DBCC Commands

These commands are use to gather and display various types of information.

1. CONCURRENCYVIOLATION – It is  maintained for backward compatibility. It runs but returns no data.

DBCC CONCURRENCYVIOLATION

2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.

DBCC INPUTBUFFER (52)

3. OPENTRAN – Displays information about the oldest active transaction and the oldest distributed and no distributed replicated transactions, if any, within the specified database.

DBCC OPENTRAN;

4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

DBCC OUTPUTBUFFER (52)

5. PROCCACHE – Displays information in a table format about the procedure cache.

DBCC PROCCACHE

6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table

USE Database Name

DBCC SHOW_STATISTICS (‘Table name’, statistics name)

7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.

USE Database Name

DBCC SHOWCONTIG (‘Table Name’);

8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

DBCC SQLPERF(LOGSPACE)

9. TRACESTATUS – Displays the status of trace flags.

DBCC TRACESTATUS(-1)

10. USEROPTIONS – Returns the SET options active (set) for the current connection.

DBCC USEROPTIONS

Validation DBCC Commands

Validation DBCC commands Performs validation operations on a database, table, index, catalog, file group, or allocation of database pages.

1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC (Database name)

2. CHECKCATALOG – Checks for catalog consistency within the specified database.

DBCC CHECKCATALOG (Database Name)

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

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.

DBCC CHECKDB (Database name)

5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroups of the current database.

USE Database Name

DBCC CHECKFILEGROUP

6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.

USE Database name;

DBCC CHECKIDENT (‘Table name)

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

USE Database name;

DBCC CHECKTABLE (‘table name’)

Miscellaneous DBCC Commands

Miscellaneous DBCC Command Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.

DBCC xp_sample (FREE)

2. TRACEOFF – Disables the specified trace flags.

DBCC TRACEOFF (3205)

3. HELP – Returns syntax information for the specified DBCC command.

Ø  List all the DBCC commands

DBCC HELP (‘?’)

Ø  Show the Syntax for a given DBCC commnad

DBCC HELP (‘DBCC command name)

4. TRACEON – Enables the specified trace flags.

DBCC TRACEON (3205)