Close Menu

SQL-SERVER

 

Most Useful DBCC Commands for MSSQlserver


Before You Proceed We recommend you to read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.


DBCC Mean Database Console Command

Here is a List of the most useful and used DBCC commands
DBCC HELP(<dbcc command>).It displays the syntax of a DBCC command.

Let us see see how to apply this for "CHECKDB"

 

DBCC Commands For Trace setp

Command:

 DBCC TRACEON

Usage: Enabling a Trace on SQL Server

Syntax: DBCC TRACEON


Command:

 DBCC TRACEOFF

Usage: Removing a Trace on SQL Server

Syntax: DBCC TRACEOFF


Command:

 DBCC TRACESTATUS

Usage: Qeriying the Trace status on SQL Server

Syntax: DBCC TRACESTATUS


Command:

 DBCC MEMORYSTATUS

Usage: displays SQL Server memory allocation status

Syntax: DBCC MEMORYSTATUS


Command:

 DBCC  Opentran 

Usage: t displays information about oldest active transaction and oldest distributed and non-distributed replication transaction , Mainly used to identify blocking

Syntax: DBCC Opentran


Command:

 DBCC  Cleantable

Usage: It is used to reclaim space from dropped variable length columns in tables/indexed views

Syntax: DBCC Cleantable (tablename)


Command:

 DBCC CHECKCATALOG

Usage: It is used to perform consistency check for a table/catalog for a particular database.

Syntax: DBCC CHECKCATALOG


Command:

 DBCC  CHECKCONSTRAINTS

Usage:It is used to check integrity of a particular constraint or all constraints on a particular table for a database.

Syntax: DBCC CHECKCONSTRAINTS


Command:

 DBCC  CHECKALLOC

Usage: It is used to check page usage and allocation of a database

Syntax: DBCC CHECKALLOC(tablename)


Command:

 DBCC  CHECKTABLE

Usage:It is used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table

Syntax: DBCC CHECKTABLE(tablename)


Command:

 DBCC SQLPERF

Usage:Displays T-log size and space used % for all databases.

Syntax: DBCC DBCC SQLPERF (logspace)


Command:

DBCC SHOWFILESTATS

Usage:Displays Used space and data file's location .

Syntax: DBCC SHOWFILESTATS(logspace)


Command:

DBCC CHECKIDENT

Usage:Displays T-log size and space used % for all databases.

Syntax: DBCC CHECKIDENT (tablename)


Command:

DBCC USEROPTIONS

Usage:It displays the active SET options for a particular database.

Syntax: DBCC USEROPTIONS (username)


Command:

DBCC  INPUTBUFFER

Usage:It is used to get the last statement sent by session to SQL server.

Syntax: DBCC INPUTBUFFER(sessionid)


Command:

DBCC SHRINKDATABASE

Usage:It is used to shrink a particular database releasing free space.

Syntax: DBCC SHRINKDATABASE(databasename)


Command:

DBCC SHRINKFILE

Usage:It is used to shrink It is used to shrink individual database files.

Syntax: DBCC SHRINKFILE(file_id)


Command:

DBCC LOGINFO

Usage:It displays virtual log file information contained in a T-log file.

Syntax: DBCC SHRINKFILE