DBCC Commands @ SQL Server

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency.

They are grouped as:

Maintenance

Miscellaneous

Validation

A brief description of the DBCC command categories is given below.

Maintenance: Maintenance tasks on a database, index, or filegroup.

Miscellaneous: Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

Informational: Tasks that gather and display various types of information.

Validation: Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

Uses Of DBCC:

DBCC give details in form of statistics about the SQL Server. They can be used for Maintenance of database, index, or filegroup. DBCC Commands can be used to perform validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

They can perform miscellaneous tasks such as enabling trace flags or removing a DLL from memory. DBCC INDEXDEFRAG, DBCC ERRORLOG, DBCC CACHESTATS are one of the few DBCC commands

List of frequently used DBCC Command

  1. DBCC CHECKCATALOG

Used to perform consistency check for a table/catalog for a particular database.

  1. DBCC CHECKCONSTRAINTS

Used to check integrity of a particular constraint or all constraints on a particular table for a database.

  1. DBCC CHECKALLOC

Used to check page usage and allocation of a database.

  1. DBCC CHECKTABLE(tablename)

Used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table.

  1. DBCC SQLPERF(logspace)

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

  1. DBCC SHOWFILESTATS

Display Extent information for a database.

  1. DBCC CHECKIDENT(tablename)

Used to check identity information or to return current identity value for a particular table.

  1. DBCC TRACEOFF

Used to disable tracing

  1. DBCC TRACEON

Used to enable tracing.

  1. DBCC TRACESTATUS

Displays the trace status.

  1. DBCC USEROPTIONS

Displays the active SET options for a particular database

  1. DBCC INPUTBUFFER(sessionid)

Used to get the last statement sent by session to SQL server.

  1. DBCC SHRINKDATABASE(databasename)

Used to shrink data and log files for a particular database. It release the free space to OS.

  1. DBCC SHRINKFILE(file_id)

Used to shrink individual database files.

  1. DBCC LOGINFO

Displays virtual log file information contained in a T-log file.

  1. DBCC HELP(<dbcc command>)

Displays the syntax of a DBCC command. To know syntax of DBCC SQLPERF, execute

  1. DBCC MEMORYSTATUS

Displays SQL Server memory allocation status.

  1. DBCC Cleantable

Used to reclaim space from dropped variable length columns in tables/indexed views

  1. DBCC Opentran

Displays information about oldest active transaction and oldest distributed and non-distributed replication transaction

For more commands, please log on to: http://sqlschool.com/SQL-DBA-Course.html

or email to our trainer: saiphanindrait@yahoo.com
We offer free technical support for this type of informational queries.

About sqlschool

SQL School (Regd: SequelGate Innovative Technologies Pvt. Ltd.) is one of fastest growing organization delivering Realtime Training and Realtime Projects exclusively on Microsoft SQL Server. Our Training services include SQL DBA Online Training, MSBI Online Training, SQL DBA Classroom Training, MSBI Classroom Training and Realtime Projects. SQL School, established in February, 2008 is now, one of the best institute offering Trainings on SQL Server, SQL Database Administration (DBA) and Business Intelligence (BI) technologies. We have been working with numerous consultancies in India, US, UK and Australia. We undertake Online Trainings and Corporate Trainings on SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012 (DENALI) versions. We make sure that all our sessions are very much interactive and well structured. We encourage every participant to come up with his / her own queries during & after the training sessions. We prefer practical approach rather than theoretical information to master the technical depth of the technology. We provide excellent Lab Handouts for practice, Realtime Case Studies and Projects on SQL Server Administration (SQL DBA) and Microsoft Business Intelligence (MS BI) Training courses. Free LIVE DEMOs would be provided prior to training registration. Specific DAY to DAY Course Plan will also be shared prior to training registration to ensure transparency of our Training services.