Useful DBCC Commands | SansSQL

Wednesday, January 27, 2010

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


Some of the useful DBCC commands along with their syntax and examples are listed down and are available for download. Click here to download your copy now.
Reference: BOL

1 comment:

ashish malwal said...

Really Great Work on this post really helpful thanks

Post a Comment