List of All Undocumented DBCC Commands in SQL Server

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the undocumented DBCC commands. Undocumented DBCC commands are used by Microsoft support to get more insights about internal behavior.

Note: Please note that it is not recommended to use the undocumented DBCC commands and Microsoft does no provide any support for the behaviour caused by using undocumented DBCC commands.

DBCC AddExtendedProc(procname, DLL) – This command adds an extended procedure to the list maintained. It has same functionality of sp_addextendedproc stored procedure.

DBCC ADDINSTANCE(Object,Instance) – This command is used to add an object’s instance to the performance monitor.

DBCC BYTES(StartingAddress, Length) – This command returns the content of the memory area beginning at StartinAddress for length BYTES.

DBCC CALLFULLTEXT(FuncID[,CatID][,ObjID]) – This command is used to perform a variety if Full-Text related functions.

DBCC DBRecover(DBName) – This command can be used to manually recover the database. Normally databases are recovered at system startup, but for some reason if they fail, we can use this to recover the database manually.

DBCCC DBTable(DBID) – This command lists internal structures DB Table and FCB (File Control Block).

DBCC DELETEINSTANCE(Object,Instance) – This command is used to delete an object’s instance from the performance monitor which was previously set using ADDINSTANCE.

DBCC DES(DBID,ObjID) – This command lists system level descriptive information of the specified object.

DBCC DetachDB(DBName) – This command can be used to detach a database from the SQL Server instance.

DBCC DROPCLEANBUFFERS – This command can be used to clear or remove all the data from the memory.

DBCC DropExtendedProc(procname, DLL) – This command drops an extended procedure from the list maintained. It has same functionality of sp_dropextendedproc stored procedure.

DBCC ERRORLOG – This command closes the current errorlog and creates a new errorlog. This command is similar to sp_cycle_errorlog.

DBCC ExtentInfo(DBName, TableName, IndexID) – This command is used for listing the extent information for specified object.

DBCC FLUSHPROCINDB(DBID) – This command is used to force all the storedProcedures to be recomplied in specified database.

DBCC IND(DBID, ObjID[,PrintOpt{0|1|2}]) – This command can be used to list the system level index information for the specified object.

DBCC LockObjectsSchema(ObjectName) – This can be used to block other connections from modifying the schema of the specified object.

DBCC LOG(DBID) – This command is used to display the log record information from the specified database transaction log.

DBCC PRTIPAGE(DBID, ObjID, IndexID[,PrintOpt{0|1|2}]) – This command can be used to list the PAGE information for the specified index.

DBCC Resource – This can be used to list the resource usage information of the server.

DBCC TAB(DBID, ObjID[,PrintOpt{0|1|2}]) – This command lists system related information of specified table.

DBCC UpgradeDB(DBName) – This command is used to upgrade the system objects of specified database to the version of database engine.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

List of SQL Server DBCC Commands and Description related to Maintenance

SQLServerF1

As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.

Below are some of the DBCC commands related to maintenance tasks for Database, Index, or Filegroup and their description.

DBCC HELP – Returns syntax information for the specified DBCC command.

DBCC INDEXDEFRAG – Defragments indexes of the specified table or view. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, therefore improving index-scanning performance.

DBCC DBREINDEX – Rebuilds one or more indexes for a table in the specified database. DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means that an index can be rebuilt without knowing the structure of a table or its constraints. This might occur after a bulk copy of data into the table.

DBCC SHRINKDATABASE – Shrinks the size of the data and log files in the specified database. To shrink all data and log files for a specific database, execute the DBCC SHRINKDATABASE command. To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command. DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is retained.

DBCC DROPCLEANBUFFERS – Removes all clean buffers from the buffer pool. Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

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. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

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. Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log will contain the following informational message.

DBCC UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.