Sunday, 25 August 2013

Useful DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.
Database Console Command statements are grouped into the following categories.
1. Maintenance
2. Miscellaneous
3. Informational
4. Validation

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

Some of the Maintenance DBCC Statements are:
DBCC CLEANTABLE
DBCC INDEXDEFRAG
DBCC DBREINDEX
DBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC SHRINKFILE
DBCC UPDATEUSAGE

Some of the Miscellaneous DBCC Statements are:
DBCC dllname (FREE)
DBCC HELP
DBCC FREESESSIONCACHE 
DBCC TRACEOFF
DBCC FREESYSTEMCACHE
DBCC TRACEON

Some of the Informational DBCC Statements are:
DBCC INPUTBUFFER
DBCC SHOWCONTIG
DBCC OPENTRAN
DBCC SQLPERF
DBCC OUTPUTBUFFER
DBCC TRACESTATUS
DBCC PROCCACHE
DBCC USEROPTIONS
DBCC SHOW_STATISTICS

Some of the Validation DBCC Statements are:
DBCC CHECKALLOC
DBCC CHECKFILEGROUP
DBCC CHECKCATALOG
DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS  
DBCC CHECKTABLE
DBCC CHECKDB