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,

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)

    SSMS

    Connect SQL Server and select TEST database, where you want to try those DBCC commands.

  • DBCC help (‘?’)

    help

    We will start from DBCC Help, as you can see in the bottom all DBCC Commands are displayed there.

  • DBCC TRACEON

    TRACEON

    In Sp_who2, we will see SPID and just type DBCC TRACEON (SPID),

    code

    To set Trace off, just type DBCC TRACEOFF (SPID).

    code

    If there is any DLL, that’s accumulating memory, like stored procedures and so on.

  • DBCC dllname (FREE)

    code

  • CHECKTABLE
    1. USE DATABASENAME  
    2. DBCC CHECKTABLE (‘TABLENAME’)  
    It checks the pages in that table and its integrity constraints.

    code

  • CHECKIDENT
    1. USE DATABASENAME  
    2. DBCC CHECKIDENT (‘TABLENAME’)  
    CHECKIDENT

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

  • CHECKDB
    1. USE MASTER  
    2. DBCC CHECKDB (‘DATABASENAME’)  
    CHECKDB

    CHECKDB

    CHECKDB

    You will see a detailed description which is in that database.

  • CheckFilegroup

    CheckFilegroup

    It gives a detailed description of allocation, tables, rows, indexes, views and so on.

  • CHECKALLOC

    CHECKALLOC

    It checks for the allocation and disk space of that database.

  • DBCC Inputbufer

    DBCC Inputbufer

    DBCC inputbuffer will give you a detailed information, which query is running; not just in parameter but SPID.

  • CLEANTABLE
    1. DBCC CLEANTABLE(DATABASENAME,’TABLENAME’,0)  
    CLEANTABLE

    Cleantable clears the space in the table.

  • DropcleanBuffers

    DropcleanBuffers

    Removes all buffer from the pool.

  • DBCC FREEPROCCACHE

    Delete all the elements in procedure cache,

    DBCC FREEPROCCACHE

  • DBCC SHRINKDATABASE

    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

    DBCC SHRINKFILE

    You have to specify the log file of the database and it will shrink log file again in MB.

  • DBCC SQLPERF(LOGSPACE)

    DBCC SQLPERF(LOGSPACE)

    It will show the log size of each database in MB and log space used.

  • DBCC OPENTRAN

     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

    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

    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.