Categories of DBCC Commands
Based
on their uses, DBCC commands are made of three categories of statements. They
are:
|
Category
|
Uses
|
Commands
|
|
Maintenance statements
|
Maintenance tasks
|
DBCC DBREINDEX, DBCC DBREPAIR, DBCC INDEXDEFRAG, DBCC
SHRINKDATABASE, DBCC SHRINKFILE, DBCC UPDATEUSAGE, DBCC CLEANTABLE, DBCC
DROPCLEANBUFFERS, DBCC FREEPROCCACHE
|
|
Status statements
|
Status checks
|
DBCC INPUTBUFFER, DBCC OPENTRAN, DBCC OUTPUTBUFFER, DBCC
PROCCACHE, DBCC SHOWCONTIG, DBCC SHOW_STATISTICS, DBCC SQLPERF, DBCC TRACESTATUS,
DBCC USEROPTIONS
|
|
Validation statements
|
Validation operations on a database and database components such
as table, index, file catalog, etc
|
DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKCONSTRAINTS, DBCC
CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKIDENT, DBCC CHECKTABLE,
DBCC NEWALLOC
|
|
Miscellaneous
statements
|
Miscellaneous tasks
|
DBCC dllname (FREE),
DBCC HELP, DBCC PINTABLE, DBCC ROWLOCK, DBCC TRACEOFF, DBCC TRACEON, DBCC
UNPINTABLE
|
Operation of DBCC statements
DBCC DBREINDEX
This statement is used to recreate the indexes
for a particular table. This statement rebuilds indexes in a single step. It
also assigns fresh pages to reduce internal and external fragmentation.
DBCC DBREPAIR
This statement is used to drop or delete a
damaged database. However, this command is no longer available with Microsoft SQL Server
2005 and later versions of Microsoft SQL Server. Instead, it has been replaced by the DROP
DATABASE Transact-SQL statement
DBCC INDEXDEFRAG
This statement is used to defragment the
clustered and secondary indexes associated with the particular table. The index defragmentation is carried out using
the fill factor specified at the time of creation of indexes. While its operation is strikingly similar to
that of DBCC DBREINDEX, unlike DBCC INDEXFRAG it does not allow new fill factor
to be specified.
DBCC SHRINKDATABASE
This statement is used to reduce the size of a
database. This statement reduces the physical size of the database log file. An
alternate way to shrink a database is to use the commander ALTER DATABASE.
DBCC SHRINKFILE
This
statement is used to reduce the size of a data file or log file of a particular
database. The file could also be
shrunk by using the SHRINKFILE attribute of the ALTER DATABASE command.
DBCC UPDATEUSAGE
This statement is used to correct inaccuracies
in the page and row statistics in the views.
DBCC CLEANTABLE
This statement is used to remove spaces occupied
by columns when they are removed. This feature is not available with Microsoft
SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005.
DBCC DROPCLEANBUFFERS
This statement is used to drop clean buffers
from the buffer pool. This feature is not available with Microsoft SQL Server
2000 and has been newly introduced in Microsoft SQL Server 2005.
DBCC FREEPROCCACHE
This statement is used to remove all elements
from the procedure cache. This feature is not available with Microsoft SQL
Server 2000 and has been newly introduced in Microsoft SQL Server 2005.
DBCC INPUTBUFFER
This statement is used to display the last
statement stored in the buffer.
DBCC OPENTRAN
This statement is used to display information
about the oldest open transaction.
DBCC OUTPUTBUFFER
This statement is used to return the current
value of the output buffer.
DBCC PROCCACHE
This statement is used to display information
about procedure cache.
DBCC SHOWCONTIG
This statement is used to display fragmentation
information
DBCC SHOW_STATISTICS
This statement is used to show current
distribution statistics
DBCC SQLPERF
This statement is used to show transaction log
statistics
DBCC TRACESTATUS
DBCC USEROPTIONS
This statement is used to return set as ACTIVE
DBCC CHECKALLOC
This statement is used to checks whether every
extent allocated by the system has been allocated and whether there are extents
that have not been allocated.
DBCC CHECKCATALOG
This statement is used to check for consistency
between system
tables in the system catalog.
It does so through cross-referencing checks.
DBCC CHECKCONSTRAINTS
DBCC CHECKDB
This
statement is used to check integrity and allocation of specific objects in
database. It also perforns DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC
CHECKCATALOG in the particular order.
DBCC CHECKFILEGROUP
This statement is used to check allocation and
structural integrity of tables.
DBCC CHECKIDENT
This statement is used to check identity value
of specified table.
DBCC CHECKTABLE
This statement is used to check the integrity of
a table and all the pages and
structures which comprise the table. Both physical and logical checks are
performed in this case. However, we can also use a PHYSICAL ONLY option to
check for physical consistency alone.
DBCC NEWALLOC
DBCC NEWALLOC is almost similar to DBCC
CHECKALLOC. This statement is not supported by recent versions.
DBCC dllname (FREE)
DBCC HELP
This statement is used to return syntax
information.
DBCC PINTABLE
This statement is used to mark a particular
table to be pinned.
DBCC ROWLOCK
This statement is used to enable Insert Row
Locking (IRL) operations.
DBCC TRACEOFF
This statement is used to disable a trace flag.
DBCC TRACEON
This statement is used to turn on a specific
trace flag.
DBCC UNPINTABLE
This statement is used to mark a table as unpinned.
In an unpinned table, the table pages in the cache could be easily removed.
Running a Database Console Command
A
database console command could be run from (i) the command window or (ii) query
analyzer window.
Advantages of Database Console Commands
Database
Console Commands have a number of advantages. Their use is extremely essential
in some instances
§
Occasionally, there have
been bad allocations of database pages.
§
Indexes could be
destroyed or corrupted easily.
§
There could
misunderstandings o part of the SQL server engine.
§
There could be problems
when a large number of updates need to be carried out.
§
Individual pages may
lose their optimal storage footprint.
No comments:
Post a Comment