Database Console Commands In SQL Server

Overview

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,

• SQL Query Plans Each Developer Should Know

Let's see what DBCC commands can do in SQL Server and we will look in each DBCC command. Hence, let’s start

Introduction

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. 

Let’s Start

• 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)
  
  
  
  
• CHECKTABLE
  

  USE DATABASENAME
  DBCC CHECKTABLE (‘TABLENAME’)

  It checks the pages in that table and its integrity constraints.
  
  
  
  
• CHECKIDENT
  

  USE DATABASENAME
  DBCC CHECKIDENT (‘TABLENAME’)

  
  
  As you see in the screenshot above, it checks the integrity value and the column value of that table.
  
  
• CHECKDB
  

  USE MASTER
  DBCC CHECKDB (‘DATABASENAME’)

  
  
  
  
  
  
  You will see a detailed description which is in that database.
  
  
• CheckFilegroup
  
  
  
  It gives a detailed description of allocation, tables, rows, indexes, views and so on.
  
  
• CHECKALLOC
  
  
  
  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
  

  DBCC CLEANTABLE(DATABASENAME,’TABLENAME’,0)

  
  
  Cleantable clears the space in the table.
  
  
• DropcleanBuffers
  
  
  
  Removes all buffer from the pool.
  
  
• DBCC FREEPROCCACHE
  
  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.
  
  
• DBCC SQLPERF(LOGSPACE)
  
  
  
  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.

Conclusion

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.