DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.
The DBCC Commands broadly falls into four categories:
A. Maintenance
B. Informational
C. Validation
D. Miscellaneous
A. Maintenance Commands
Performs maintenance tasks on a database, index, or file-group.
1. CLEANTABLE - Reclaims space from the dropped variable-length columns in tables or index views.
Example
DBCC CLEANTABLE ('AdventureWorks','Person.Contact',0)
2. DBREINDEX - Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead) USE AdventureWorks
Example
DBCC DBREINDEX ('Person.Contact','PK_Contact_ContactID',80)
3. DROPCLEANBUFFERS - Removes all clean buffers from buffer pool.
Example
DBCC DROPCLEANBUFFERS
4. FREEPROCCACHE - Removes all elements from the procedure cache
Example
DBCC FREEPROCCACHE
5. INDEXDEFRAG - Defragments indexes of the specified table or view.
Example
DBCC INDEXDEFRAG ('AdventureWorks', 'Person.Address', PK_Address_AddressID)
6. SHRINKDATABASE - Shrinks the size of the data and log files in the specified database.
Example
DBCC SHRINKDATABASE ('AdventureWorks', 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 filegroup, allowing the file to be removed from the database.
Example
USE AdventureWorks;
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1)
8. UPDATEUSAGE - Reports and corrects pages and row count inaccuracies in the catalog views.
Example
DBCC UPDATEUSAGE (AdventureWorks)
B. Informational Commands
Performs tasks that gather and display various types of information.
1. CONCURRENCYVIOLATION - is maintained for backward compatibility. It runs but returns no data.
Example
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 nondistributed 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 AdventureWorks
DBCC SHOW_STATISTICS ('Person.Address', AK_Address_rowguid)
7. SHOWCONTIG - Displays fragmentation information for the data and indexes of the specified table or view.
USE AdventureWorks
DBCC SHOWCONTIG ('HumanResources.Employee');
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
C. Validation Commands
Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
1. CHECKALLOC - Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKALLOC (AdventureWorks)
2. CHECKCATALOG - Checks for catalog consistency within the specified database.
DBCC CHECKCATALOG (AdventureWorks)
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 (AdventureWorks)
5. CHECKFILEGROUP - Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
USE AdventureWorks
DBCC CHECKFILEGROUP
6. CHECKIDENT - Checks the current identity value for the specified table and, if it is needed, changes the identity value.
USE AdventureWorks;
DBCC CHECKIDENT ('HumanResources.Employee')
7. CHECKTABLE - Checks the integrity of all the pages and structures that make up the table or indexed view.
USE AdventureWorks;
DBCC CHECKTABLE ('HumanResources.Employee')
D. Miscellaneous Commands
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 ('checkcatalog')
4. TRACEON - Enables the specified trace flags.
DBCC TRACEON (3205)
No comments:
Post a Comment