DBCC Commands in SQL Server
SQL Server has so many DBCC commands which are available in documentation & a few DBCC commands are not in documents. DBAs should be aware of these DBCC commands to perform their daily tasks.
In this article, we'll try to explain & give in-depth knowledge about the DBCC commands, types of DBCC commands based on categories, etc.
Introduction
DBCC stands for Database Console Commands. DBCC commands are very powerful tools that help database administrators to maintain and troubleshoot database performance, integrity, index maintenance, memory allocation management, and consistency.
Microsoft has grouped Database Console Commands in the below categories:
DBCC Category
Command category
Perform
Maintenance
Maintenance tasks on a database, index, or filegroup.
Miscellaneous
Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational
Tasks that gather and display various types of information.
Validation
Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
DBCC Commands related to Maintenance
DBCC FREEPROCCACHE
DBCC CLEANTABLE
DBCC INDEXDEFRAG
DBCC DBREINDEX
DBCC SHRINKDATABASE
DBCC SHRINKFILE
DBCC DROPCLEANBUFFERS
DBCC UPDATEUSAGE
DBCC Commands related to Miscellaneous
DBCC HELPDBCC FLUSHAUTHCACHEDBCC TRACEOFFDBCC FREESESSIONCACHEDBCC TRACEONDBCC FREESYSTEMCACHEDBCC dllnameDBCC CLONEDATABASE
DBCC Commands related to Validation
DBCC CHECKALLOC
DBCC CHECKCATALOG
DBCC CHECKCONSTRAINTS
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKIDENT
DBCC CHECKTABLE
DBCC Commands related to Information
DBCC INPUTBUFFER
DBCC SHOWCONTIG
DBCC OPENTRAN
DBCC OUTPUTBUFFER
DBCC PROCCACHE
DBCC SHOW_STATISTICS
DBCC SQLPERF
DBCC TRACESTATUS
DBCC USEROPTIONS
Understanding DBCC Commands
DBCC commands are a combination of utility commands in SQL Server that help us to perform database administration tasks. It helps administrators to control and manage databases and perform database consistency checks, repair data corruption, indexes management, clear cache, etc. We can run the DBCC command in SSMS to perform the task.
1) DBCC TRACESTATUS:
*******************
Usage:
This DBCC command displays the status of any trace flag. We need to pass a trace flag ID to know the current status.
Example:
DBCC TRACESTATUS(1205);
Status = 1 means the trace flag 1205 is enabled.
Benefits:
This command helps us to check whether any particular trace flag is in the enable state or not.
2) DBCC TRACEON:
***************
Usage:
We use Traceon to enable specified trace flags.
Example:
DBCC TRACEON (1205);
go
DBCC TRACEON (1205,-1);
go
When we specify -1 with any trace flag, it means to are enabling the trace flag globally.
Even, we can enable more than one trace flag in one go.
DBCC TRACEON (1205, 1222, -1);
go
Benefits:
When we need more details for a particular activity, need to enable a specific trace flag in the system.
3) DBCC TRACEOFF:
***************
Usage:
We use TraceOff to disable specified trace flags.
Example:
DBCC TRACEOFF (1205);
go
DBCC TRACEOFF (1205,-1);
go
We can use -1 with any trace flag to disable specified trace flags globally.
We can disable more than one trace flag in one go.
DBCC TRACEON (1205, 1222, -1);
go
Benefits:
To suppress additional information in the log file, use the traceoff command.
4) DBCC CHECKDB:
***************
Usage:
To check logical and physical integrity in the database, we can use the DBCC CHECKDB command.
This command checks the structural and allocation integrity of database objects, like tables, indexes, and system catalog views, and reports any potential errors or corruption within the database.
Example:
DBCC CHECKDB('DatabaseName')
Benefits:
This command Identifies and repairs database corruptions and ensures data integrity.
It also provides information on the allocation and structural errors of a database.
It prevents potential data loss as well as maintains database reliability.
Also assists us in diagnosing and resolving performance-related issues of a database.
If the outcome of DBCC CHECKDB shows the below details, it means our database is free from any error.
CHECKDB found 0 allocation errors and 0 consistency errors in the database 'test'.
No comments:
Post a Comment