DbccCommand Enumeration
February 22nd, 2010The SMO libraries contain an enum for the DBCC commands: DbccCommand. But there is little explanation in the MSDN documentation what those commands are. I had a recent discussion with a developer who, understandably, expected a little cleaner explanation for the meaning of the mysterious enum values. So I decided to compile a table of links to the corresponding SQL Server DBCC command documentation. For well known undocumented commands, I’ve linked popular community articles. Some undocumented DBCC commands are very dangerous to use, obsolete or some are only for internal use, or simply I have no idea what they do, and I did not add any explanation for them.
| Command | Documentation | Comments |
|---|---|---|
| ActiveCursors | ||
| AddExtendedProc | ||
| AddInstance | ||
| AuditEvent | ||
| AutoPilot | Used by the Tunning Advisor | |
| Buffer | Display pages from the buffer pool. | |
| Bytes | Reads the SQL Server process memory. | |
| CacheProfile | ||
| CacheStats | ||
| CallFullText | ||
| CheckAlloc | DBCC CHECKALLOC | Checks the consistency of disk space allocation structures for a specified database. |
| CheckCatalog | DBCC CHECKCATALOG | Checks for catalog consistency within the specified database. |
| CheckConstraints | DBCC CHECKCONSTRAINTS | Checks the integrity of a specified constraint or all constraints on a specified table in the current database. |
| CheckDB | DBCC CHECKDB | Checks the logical and physical integrity of all the objects in the specified database. |
| CheckFileGroup | DBCC CHECKFILEGROUP | Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database. |
| CheckIdent | DBCC CHECKIDENT | Checks the current identity value for the specified table and, if it is needed, changes the identity value. |
| CheckPrimaryFile | ||
| CheckTable | DBCC CHECKTABLE | Checks the integrity of all the pages and structures that make up the table or indexed view. |
| CleanTable | DBCC CLEANTABLE | Reclaims space from dropped variable-length columns in tables or indexed views. |
| ClearSpaceCaches | ||
| CollectStats | ||
| ConcurrencyViolation | Discontinued in SQL 2008 | |
| CursorStats | ||
| DBRecover | Use RESTORE DATABASE … WITH RECOVERY; instead. | |
| DBReindex | DBCC DBREINDEX | Rebuilds one or more indexes for a table in the specified database. |
| DBReindexAll | ||
| DBRepair | ||
| DebugBreak | Breaks the SQL Server process into debugger. Afaik this is inactive in the released bits. | |
| DeleteInstance | ||
| DetachDB | ||
| DBCC dllname (FREE) | Unloads the specified extended stored procedure DLL from memory. |
|
| DropCleanBuffers | DBCC DROPCLEANBUFFERS | Removes all clean buffers from the buffer pool. |
| DropExtendedProc | ||
| DumpConfig | ||
| DumpDBInfo | Displays DBINFO structure for the specified database. | |
| DumpDBTable | Displays the contents of the DBTABLE structure | |
| DumpLock | ||
| DumpLog | ||
| DumpPage | ||
| DumpResource | ||
| DumpTrigger | DBCC DUMPTRIGGER | Creates a process dump when an exception is raised |
| ErrorLog | Recycles the errorlog. Use sp_cycle_errorlog instead. | |
| ExtentInfo | ||
| FileHeader | ||
| FixAllocation | ||
| Flush | ||
| FlushProcInDB | ||
| ForceGhostCleanup | Runs the ghost cleanup. | |
| Free | ||
| FreeProcCache | DBCC FREEPROCCACHE | Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool. |
| FreeSessionCache | DBCC FREESESSIONCACHE | Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server. |
| FreeSystemCache | DBCC FREESYSTEMCACHE | Releases all unused cache entries from all caches. |
| FreezeIO | This is the ‘freeze’ phase of the SQL Writer VSS based database snapshot backup, as documented in A Guide for SQL Server Backup Application Vendors | |
| Help | DBCC HELP | Returns syntax information for the specified DBCC command. |
| IcecapQuery | Icecap is the name of one of the code profilers used inside Microsoft. | |
| IncrementInstance | ||
| Ind | Shows all pages in use by indexes of the specified table. | |
| IndexDefrag | DBCC INDEXDEFRAG | Defragments indexes of the specified table or view. |
| InputBuffer | DBCC INPUTBUFFER | Displays the last statement sent from a client to an instance of Microsoft SQL Server. |
| InvalidateTextptr | ||
| InvalidateTextptrObjid | ||
| Latch | ||
| LogInfo | ||
| MapAllocUnit | ||
| MemObjList | Use sys.dm_os_memory_objects instead. | |
| MemoryMap | ||
| MemoryStatus | DBCC MEMORYSTATUS | Displays a snapshot of the current memory status of SQL Server. |
| Metadata | ||
| MovePage | Do not use it. | |
| NoTextptr | ||
| OpenTran | DBCC OPENTRAN | Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. |
| OptimizerWhatIf | ||
| OutputBuffer | DBCC OUTPUTBUFFER | Returns the current output buffer in hexadecimal and ASCII format for the specified session_id. |
| DBCC PAGE | Prints out the contents of a SQL Server page. Although not in the SMO enum MSDN documentation, I could not let this one out of any DBCC write up… | |
| PinTable | ||
| PerfMonStats | ||
| PersistStackHash | ||
| ProcCache | DBCC PROCCACHE | Displays information in a table format about the procedure cache. |
| PrtiPage | Displays the page number pointed to by each row on the specified index page. | |
| ReadPage | ||
| RenameColumn | ||
| RuleOff | ||
| RuleOn | ||
| SeMetadata | ||
| SetCpuWeight | ||
| SetInstance | ||
| SetIOWeight | ||
| ShowStatistics | DBCC SHOW_STATISTICS | Displays current query optimization statistics for a table or indexed view. |
| ShowContig | DBCC SHOWCONTIG | Displays fragmentation information for the data and indexes of the specified table or view. |
| ShowDBAffinity | ||
| ShowFileStats | ||
| ShowOffRules | ||
| ShowOnRules | ||
| ShowTableAffinity | ||
| ShowText | ||
| ShowWeights | ||
| ShrinkDatabase | DBCC SHRINKDATABASE | Shrinks the size of the data and log files in the specified database. |
| ShrinkFile | DBCC 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 filegroup, allowing the file to be removed from the database. |
| SqlMgrStats | ||
| SqlPerf | DBCC SQLPERF | Provides transaction log space usage statistics for all databases. |
| StackDump | Creates a SQL Server process mini-dump | |
| Tec | ||
| ThawIO | This is the ‘thaw’ phase of the SQL Writer VSS based database snapshot backup, as documented in A Guide for SQL Server Backup Application Vendors | |
| TraceOff | DBCC TRACEOFF | Disables the specified trace flags. |
| TraceOn | DBCC TRACEON | Enables the specified trace flags. |
| TraceStatus | DBCC TRACESTATUS | Displays the status of trace flags. |
| UnpinTable | ||
| UpdateUsage | DBCC UPDATEUSAGE | Reports and corrects pages and row count inaccuracies in the catalog views. |
| UsePlan | ||
| UserOptions | DBCC USEROPTIONS | Returns the SET options active (set) for the current connection. |
| WritePage | Do not use it. |
[...] the Ghost Cleanup was just unable to keep up with the nearly 200Gb size empty queue. Even running DBCC FORCEGHOSTCLEANUP could not improve the [...]