神刀安全网

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)
    Database Console Commands In SQL Server Connect SQL Server and select TEST database, where you want to try those DBCC commands.
  • DBCC help (‘?’)
    Database Console Commands In SQL Server We will start from DBCC Help, as you can see in the bottom all DBCC Commands are displayed there.
  • DBCC TRACEON
    Database Console Commands In SQL Server In Sp_who2, we will see SPID and just type DBCC TRACEON (SPID),
    Database Console Commands In SQL Server To set Trace off, just type DBCC TRACEOFF (SPID).
    Database Console Commands In SQL Server If there is any DLL, that’s accumulating memory, like stored procedures and so on.
  • DBCC dllname (FREE)

    Database Console Commands In SQL Server

  • CHECKTABLE
    USE DATABASENAME   DBCC CHECKTABLE (‘TABLENAME’)   

    It checks the pages in that table and its integrity constraints.
    Database Console Commands In SQL Server

  • CHECKIDENT
    USE DATABASENAME   DBCC CHECKIDENT (‘TABLENAME’)   

    Database Console Commands In SQL Server 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’)   

    Database Console Commands In SQL Server Database Console Commands In SQL Server Database Console Commands In SQL Server You will see a detailed description which is in that database.

  • CheckFilegroup
    Database Console Commands In SQL Server It gives a detailed description of allocation, tables, rows, indexes, views and so on.
  • CHECKALLOC
    Database Console Commands In SQL Server It checks for the allocation and disk space of that database.
  • DBCC Inputbufer
    Database Console Commands In SQL Server DBCC inputbuffer will give you a detailed information, which query is running; not just in parameter but SPID.
  • CLEANTABLE
    DBCC CLEANTABLE(DATABASENAME,’TABLENAME’,0)   

    Database Console Commands In SQL Server Cleantable clears the space in the table.

  • DropcleanBuffers

    Database Console Commands In SQL Server Removes all buffer from the pool.

  • DBCC FREEPROCCACHE

    Delete all the elements in procedure cache,
    Database Console Commands In SQL Server

  • DBCC SHRINKDATABASE
    Database Console Commands In SQL Server It shrinks the database, but you have to specify the size of a log; how much you want to shrink in MB.
  • DBCC SHRINKFILE
    Database Console Commands In SQL Server You have to specify the log file of the database and it will shrink log file again in MB.
  • DBCC SQLPERF(LOGSPACE)

    Database Console Commands In SQL Server It will show the log size of each database in MB and log space used.

  • DBCC OPENTRAN
    Database Console Commands In SQL Server 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
    Database Console Commands In SQL Server 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
    Database Console Commands In SQL Server 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.

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Database Console Commands In SQL Server

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址