Useful DBCC (Database Console Commands) for SQL Server.

Many DBA’s aren’t very familiar with DBCC commands because they don’t always have the hands on chance to work with them. The T-SQL (Transact-SQL) programming language provides DBCC statements that act as Database Console Commands for SQL Server. In this article, we’ll learn some useful DBCC commands with specific examples.

DBCC statements can be 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.

Note: These commands many of them require sysadmin or db_owner permission.

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

In this article, I have covered only useful DBCC commands. I hope this article has been informative for you. Hopefully I can cover the more basic/advanced commands in another article with examples. That's all in this article.