In SQL Server, we know how to trace a particular query and to find out which particular query is taking time. You can refer to this helpful article,
Let’s see what DBCC commands can do in SQL Server and we will look in each DBCC command. Hence, let’s start
DBCC stands for Database Console Commands (DBCC) or Database Consistency Checker. DBCC commands are used for troubleshooting purposes. Microsoft SQL Server has introduced many DBCC commands, and there were a few in Version 2000, 2005 and so on. With each version, they are adding new DBCC commands and these DBCC commands are helpful when you use them. When you start using them more often, you will notice how these commands are useful.
- Open SSMS (SQL Server Management Studio)
Connect SQL Server and select TEST database, where you want to try those DBCC commands.
- DBCC help (‘?’)
We will start from DBCC Help, as you can see in the bottom all DBCC Commands are displayed there.
- DBCC TRACEON
In Sp_who2, we will see SPID and just type DBCC TRACEON (SPID),
To set Trace off, just type DBCC TRACEOFF (SPID).
If there is any DLL, that’s accumulating memory, like stored procedures and so on.
DBCC dllname (FREE)
USE DATABASENAME DBCC CHECKTABLE (‘TABLENAME’)
It checks the pages in that table and its integrity constraints.
USE DATABASENAME DBCC CHECKIDENT (‘TABLENAME’)
As you see in the screenshot above, it checks the integrity value and the column value of that table.
USE MASTER DBCC CHECKDB (‘DATABASENAME’)
You will see a detailed description which is in that database.
It gives a detailed description of allocation, tables, rows, indexes, views and so on.
It checks for the allocation and disk space of that database.
- DBCC Inputbufer
DBCC inputbuffer will give you a detailed information, which query is running; not just in parameter but SPID.
Cleantable clears the space in the table.
Removes all buffer from the pool.
Delete all the elements in procedure cache,
- DBCC SHRINKDATABASE
It shrinks the database, but you have to specify the size of a log; how much you want to shrink in MB.
- DBCC SHRINKFILE
You have to specify the log file of the database and it will shrink log file again in MB.
It will show the log size of each database in MB and log space used.
- DBCC OPENTRAN
This is used to find out the transactions that are running in the database. This is mostly used to find the oldest transaction and kill it .
- DBCC SHOW_STATISTICS
This command is used to tune a query or tracing is done but you have to the pass table name and an index name .
- DBCC UPDATEUSAGE
This command is used to update data and space in sysindexes table and clears the space.
This sums up DBCC Commands in SQL Server, its uses and where we can use these commands. If you have any doubts pertaining to this topic, feel free to ask.