Q- What is DBCC command in SQL sever.
A-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.
A-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.
These are four types:
- Maintenance
- Informational
- Validation
- Miscellaneous
Maintenance Commands
Performs maintenance tasks on a database, index, or file group.
Syntax:
DBCC CLEANTABLE (‘DatabaseName’,’TableName.ColumnName’,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)
Syntax:
USE DatabaseName
DBCC DBREINDEX (‘TableName.ColumnName’,’PK_ID’,80)
3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.
Syntax:
DBCC DROPCLEANBUFFERS
4. FREEPROCCACHE – Removes all elements from the procedure cache
Syntax:
DBCC FREEPROCCACHE
5. INDEXDEFRAG – Defragments indexes of the specified table or view.
Syntax:
DBCC INDEXDEFRAG (‘DatabaseName’, ‘TableName.ColumnName’, PK_ID)
6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database\
Syntax:
DBCC SHRINKDATABASE (‘DatabaseName‘, 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 file group, allowing the file to be removed from the database.
Syntax:
USE DatabaseName;
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseName_Log, 1)
8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.
Syntax:
DBCC UPDATEUSAGE (DatabaseName)
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.
Syntax:
DBCC CONCURRENCYVIOLATION
2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.
Syntax:
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.
Syntax:
DBCC OPENTRAN;
4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.
Syntax:
DBCC OUTPUTBUFFER (52)
5. PROCCACHE – Displays information in a table format about the procedure cache.
Syntax:
DBCC PROCCACHE
6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table
Syntax:
USE AdventureWorks
DBCC SHOW_STATISTICS (‘TableName.ColumnName’, PK_ID)
7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.
Syntax:
USE DatabaseName
DBCC SHOWCONTIG (‘DatabaseName.TableName’);
8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.
Syntax:
DBCC SQLPERF(LOGSPACE)
9. TRACESTATUS – Displays the status of trace flags.
Syntax:
DBCC TRACESTATUS(-1)
10. USEROPTIONS – Returns the SET options active (set) for the current connection.
Syntax:
DBCC USEROPTIONS
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 (DatabaseName)
2. CHECKCATALOG – Checks for catalog consistency within the specified database.
Syntax:
DBCC CHECKCATALOG (DatabaseName)
3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database.
Syntax:
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.
Syntax:
DBCC CHECKDB (DatabaseName)
5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
Syntax:
USE DatabaseName
DBCC CHECKFILEGROUP
6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.
Syntax:
USE DatabaseName;
DBCC CHECKIDENT (‘DatabaseName.TableName’)
7. CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
Syntax:
USE AdventureWorks;
DBCC CHECKTABLE (‘DatabaseName.TableName’)
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.
Syntax:
DBCC xp_sample (FREE)
2. TRACEOFF – Disables the specified trace flags.
Syntax:
DBCC TRACEOFF (3205)
3. HELP – Returns syntax information for the specified DBCC command.
Syntax:
— 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.
Syntax:
DBCC TRACEON (3205)
great
ReplyDelete