List of SQL Server Miscellaneous DBCC Commands and Description
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 Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
DBCC dllname (FREE) – Unloads the specified extended stored procedure DLL from memory. When an extended stored procedure is executed, the DLL remains loaded by the instance of SQL Server until the server is shut down. This statement allows for a DLL to be unloaded from memory without shutting down SQL Server. To display the DLL files currently loaded by SQL Server, execute sp_helpextendedproc.
DBCC FREESESSIONCACHE – Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.
DBCC FREESYSTEMCACHE – Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from all caches or from a specified Resource Governor pool cache. Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the informational message.
DBCC HELP – Returns syntax information for the specified DBCC command. DBCC HELP returns a result set displaying the syntax for the specified DBCC command.
DBCC TRACEOFF – Disables the specified trace flags. Trace flags are used to customize certain characteristics controlling how the instance of SQL Server operates.
DBCC TRACEON – Enables the specified trace flags. Trace flags are used to customize certain characteristics by controlling how SQL Server operates. Trace flags, after they are enabled, remain enabled in the server until disabled by executing a DBCC TRACEOFF statement. In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server. To determine the status of trace flags, use DBCC TRACESTATUS. To disable trace flags, use DBCC TRACEOFF.
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.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.