19 January 2020

DBCC commands

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency.
They are categorized as:
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

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

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

DBCC CHECKALLOC
Used to check page usage and allocation of a database.

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(spid)
Used to get the last statement sent by session to SQL server.
  1. DBCC SHRINKDATABASE(dbname)
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

Resource Governor

Resource Governor  is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables y...