DBCC Commands

                                     DBCC Commands 

DBCC (Database consistency checker) commands are used to check the consistency of the databases. The important use of this commands is for improving performance and to troubleshoot.

These commands are grouped into the following four categories.

1       

      1. Maintenance:  Maintenance tasks on a database, index, or file group.

The following dbcc commands fall under the maintenance category:

DBCC CleanTable : This command is used to cleanup waste left around by deleting variable length columns.    

Example: DBCC CLEANTABLE(‘Learning’,Employee.Contact’,0)  

   

 In this example,‘Learning’ is the DB name

‘Employee.Contact’ is the table name or view name.

‘0’ is the batch size i.e, if you specify 0 it means the statement process the whole table in one transaction. If you specify some other value(2) it means you are specifying 2 rows to be processed per transaction.

DBCC REINDEX : This command is used to build one or more indexes for the table in the specified DB.(Note: This feature will be removed in future versions use ALTER INDEX instead.

Example : DBCC DBREINDEX(‘Employee.Contact’,’PK_Contact_ContactID’ ,90)

In this example, ‘Employee.Contact’ is the table name containing the index or indexes to rebuild.

‘PK_Contact_ContactID’ is the name of the index to rebuild.

‘90’ is the % of space on each index page for storing data when the index is created or rebuilt. If this % is not specified by default it uses 100.

DBCC DROPCLEANBUFFERS : This command is very useful while doing the performance tuning of a query. This is used to test queries with a cold buffer cache(any data needs to be loaded from a disk in order for query to run) without shutting down and restarting the server

Example : DBCC DROPCLEANBUFFERS

This example will drop all the clean buffers out of memory causing a reload from next time they are needed.

DBCC FREEPROCCACHE : Removes all the elements from the plan cache.

Example : DBCC FREEPROCCACHE 

This example clears all elements from the plan cache.

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

Example : DBCC INDEXDEFRAG(‘Learning’,’Employee.Address’,PK_Address_AddressID’)

In  this example ‘Learning’ is the DB ,‘Employee.Address’ is the table,‘PK_Address_AddressID’ is the index to be defragmented.

DBCC SHRINKDATABASE : Shrinks the size of data and log file in the specified database

Example: DBCC SHRINKDATABASE(‘Learning’,10)

In this example ‘Learning’ is the database,

‘10’ is the % of the free space that you want left in the database file after the database is shrunk.

DBCC SHRINKFILE : Shrinks the size of the specified data or log file for the specified database

Example : DBCC SHRINKFILE(‘Learning_Log’,5)

In this example ‘Learning_Log’ is the name of the file to be shrunk.

‘5’ is the size of the file in megabytes. If you don’t specify this value then it will reduce the file size to the default file size.

DBCC UPDATEUSAGE : Reports and correct pages and row count inaccuracies in the catalog views.

Example : DBCC UPDATEUSAGE(‘Learning’,’Employee.Address’,’PK_Address_AddressID’)

In this example ‘Learning’ is the DB for which to report and correct the usage statistics.

‘Employee.Address’ is the table name for which to report and correct the usage statistics.

‘PK_Address_AddressID’ is the name of the index to use. If this is not specified the statement processes all indexes for the specified table or view.

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

The following DBCC commands fall under the Miscellaneous category:

DBCC dllname (FREE) :  Unloads the specified extended stored procedure DLL from memory.

Example : DBCC xp_sample(FREE)

In this example ‘xp_sample’ is the DLL name to release from memory.

DBCC FLUSHAUTHCACHE :  This command empties the database authentication cache containing the info about the logins and firewall rules.

Example : DBCC FLUSHAUTHCACHE;

The example clears the authen tication cache for the current database.

DBCC FREESESSIONCACHE : Removes all the queries from the distributed query cache

Example : DBCC FREESESSIONCACHE

This example clears all the distributed queries from the distributed query cache.

DBCC FREESYSTEMCACHE : This is used to free all memory associated with all of the SQL Server caches.

Example : DBCC FREESYSTEMCACHE(‘Learning’)

This example clears all the cache for the Database ‘Learning’. If you want to clear everything then in place of DB name you specify ‘ALL’ .

DBCC HELP : Returns the syntax info for the specified dbcc command 

Example : DBCC HELP(‘CHECKDB’)

In this example ‘CHECKDB’ is the part of DBCC command for which to receive the syntax information. If you want to return all the dbcc commands for which help is available then use ‘?’ in place of ‘CHECKDB’ .

DBCC TRACEOFF : Disables the specified trace flag

Example : DBCC TRACEOFF(1100)

In this example ‘1100’ is the number of trace flag to disable.

DBCC TRACEON : Enables the specified trace flag

Example : DBCC TRACEON(1100)

In this example it enables the trace flag for the number ‘1100’

DBCC CLONEDATABASE : This command creates a new database that contains the schema of all the objects and statistics from the specified source database.

Example : DBCC CLONEDATABASE(‘Learning’,’Rain’)

In this example, ‘Learning’ is the source database whose schema and statistics needs to be copied.

‘Rain’ is the destination database which the schema and statistics from the source database will be copied to. This database will be created by DBCC CLONEDATABASE and should not already exist.

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

      

      The following DBCC commands fall under the Informational category:

             

     DBCC INPUTBUFFER :  This command displays the last statement sent from a client to an instance of SQL Server.

     Example : DBCC INPUTBUFFER(110)

      

      In this example ‘110 ‘ is the session id of the client.

    DBCC OPENTRAN : This commands helps to identify the active transactions that may be preventing the log truncation. Results are only displayed if there is an active transaction that exists in the log or if the database contains replication info.

      Example : DBCC OPENTRAN(‘Learning’)

    In this example ‘Learning’ is the database name for which to display the oldest transaction information. If this is not specified, or 0 is specified the current database is used.

     DBCC OUTPUTBUFFER : Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

      Example : DBCC OUTPUTBUFFER(52)

      In this example 52 is the session id associated with each active primary connection.

      DBCC PROCACHE : Displays information in table format about the procedure cache.

      Example:  DBCC PROCACHE

      This example will display the information in table format about the procedure cache.

     DBCC SHOW_STATISTICS : Displays the current distribution statistics for the specified target on the specified table

       Example :  USE Learning

DBCC SHOW_STATISTICS(‘Employee.Address’,AK_Address_rowguid)

In this example its using Database ‘Learning’ and ‘Employee.Address’ is the name of the table or indexed view.

AK_Address_rowguid is the name of the statistics.

DBCC SHOWCONTIG : Displays the fragmented information for the data and indexes of the specified table or view.

Example : USE AdventureWorks

DBCC SHOWCONTIG(‘HumanResources.Employee’)

In this example its using Database AdventureWorks and ‘HumanResources.Employee’ is the table name which displays the fragmented information and indexes.

DBCC SQLPERF : Provides transaction log space usage statistics for all the Databases.

Example : DBCC SQLPERF(LOGSPACE)

In this example ‘LOGSPACE’

Returns the current size of the transaction log and the %of logspace used for each database.

DBCC TRACESTATUS : This command displays the information as whether the trace flag is ON or Off.

Example : DBCC TRACESTATUS(65)

In this example ‘65’ is the number of the trace flag for which the status is displayed.

DBCC USEROPTIONS :  This command tells you the setting for your current session.

Example : DBCC USEROPTIONS

This example will tell you the useroptions for your current session.

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

The following DBCC commands fall under the Validation category:

DBCC CHECKALLOC : Checks the consistency of Disk space allocation structure for a specified Database.

Example : DBCC CHECKALLOC(Learning)

This example will check allocation and page usage for the database Learning.

DBCC CHECKCATALOG : Checks the catalog consistency within the specified Database. The database should be online.

Example : DBCC CHECKCATALOG(Learning)

This example will check the catalog consistency for the database learning.

DBCC CHECKCONSTRAINTS : Checks the integrity of one constraint, all constraints for a table, or all constraints for a database.

Example : DBCC CHECKCONSTRAINTS(‘Employee.Address’)

This example is checking the constraints for the table ‘Employee.Address’.

If you want to check the single constraint in a table then in place of table name write the Constraint name

DBCC CHECKDB : Checks the logical and physical integrity of all the objects in the specified database. If corruption has occurred for any reason then this command tell you exactly what the problem is. This command uses few optional arguments to help fix your corrupted db.

Example : DBCC CHECKDB(AdventureWorks)

This example checks the logical and physical integrity of all the objects for DB ‘AdventureWorks’

DBCC CHECKFILEGROUP :  Is used for a specific file group to check the disk allocation and structural integrity of all tables and indexed views.

Example : USE Learning

GO

Select * from sys.filegroups;

 DBCC  CHECKFILEGROUP(‘PRIMARY’)

In this example, ‘PRIMARY’ is the file group name.

You can get the file group name by running the select * from sys.filegroups on the specified database in our case the database is ‘Learning’

DBCC CHECKIDENT : This command is used to check on the current value in the identity column for a table.This command can also be used to update or set the next identity value on a table.

Example : DBCC CHECKIDENT(‘Employee.Address’,NORESEED|RESEED,new_reseed_value)

In this example ‘Employee.Address’ is the table name

NORESEED specifies that the current identity value should not be changed.

RESEED specifies the current identity value should be changed.

New_reseed_value is the new value to use as the current value of the identity column.

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

Example : DBCC CHECKTABLE(‘Employee.Address’)

This example checks the integrity for the table ‘Employee.Address’