Common DBCC command classification statistics

Today, I used all the DBCC commands one by one and made notes to avoid forgetting them. some parameters are used to call the command. 'taobaoitem _ 8080' indicates the table name, 'ix _ taobaoitem_0003 'indicates the index, and 'taobao' indicates the database name, 'taobao3 'is a database file.

DBCC is provided in the transact-SQL programming language.
SQL Server
Database Console Commands.

Database Console Command statements can be divided into the following categories.

Command type	Run
Maintenance	Maintenance Tasks for databases, indexes, or file groups.
Miscellaneous	Miscellaneous tasks, such as enabling trace flag or deleting DLL from memory.
Information	A task that collects and displays various types of information.
Verify	Verifies the distribution of databases, tables, indexes, directories, file groups, or database pages.

DBCC commands use input parameters and return values. All DBCC
Both Unicode and DBCS can be used for command parameters.
Text.

Information statement

DBCC inputbuffer: Displays the last statement sent from the client to the Microsoft SQL Server 2005 instance.

Format: DBCC inputbuffer (session_id [, request_id]) [with no_infomsgs]

DBCC outputbuffer:Returns the specified value in hexadecimal or ASCII format.
Session_idThe current output buffer.

Format: DBCC outputbuffer (session_id [, request_id]) [with no_infomsgs]

DBCC showcontig: Displays the data and index fragmentation information of the specified table or view.

For example, DBCC showcontig
('Taobaoitem _ 000000 ')

DBCC opentran: If the earliest active transactions and the earliest distributed and non-distributed replication transactions exist in the specified database, the related information is displayed.

DBCC sqlperf: Provides statistics on transaction log space usage for all databases. It can also be used to reset the statistical information of the wait and lock.

Format: DBCC sqlperf ([logspace] | ["SYS. dm_ OS _latch_stats", clear] | ["SYS. dm_ OS _wait_stats", clear]) [with no_infomsgs]

For example, DBCC sqlperf
(Logspace)

DBCC sqlperf
("SYS. dm_ OS _wait_stats"
, Clear)

DBCC tracestatus: Displays the status of the trace flag.

DBCC proccache: Process cache information is displayed in table format.

DBCC useroptions: Return the set Option of the current connection activity (setting.

DBCC show_statistics: Displays the current distribution statistics of the specified target in the specified table.

For example, DBCC show_statistics ('taobaoitem _ 000000', 'ix _ taobaoitem_0003 ')

Verification statement

DBCC checkalloc: Check whether the disk space allocation structure of the specified database is consistent.

For example, DBCC checkalloc
('Taobao ')

DBCC checkfilegroup: Check the distribution and structural integrity of all tables and index views in the specified file group in the current database.

For example, DBCC checkfilegroup
('Taobao3 ')

DBCC checkcatalog: Check the directory consistency in the specified database.

For example, DBCC checkcatalog
('Taobao ')

DBCC checkident: Check the current ID value of the specified table. If necessary, change the id value.

For example, DBCC checkident
('Taobaoitem _ 000000 ')

DBCC checkconstraints: Check the integrity of the specified or all constraints on the specified table in the current database.

DBCC checktable: Check the integrity of all pages and structures in a table or index view.

DBCC checkdb: Perform the following operations to check the logical and physical integrity of all objects in the specified database:

• Run DBCC checkalloc on the database.
• Run DBCC checktable for each table and view in the database.
• Run DBCC checkcatalog on the database.
• Verify the content of each index view in the database.
• Verify the service broker data in the database.

Maintenance statement

DBCC cleantable: Reclaim the space of the deleted variable-length columns in a table or index view.

For example, DBCC cleantable
('Taobao', 'taobaoitem _ 000000 ')

DBCC indexdefrag:Specify the index fragmentation for the table or view.

For example, DBCC indexdefrag
('Taobao', 'taobaoitem _ 000000 ')

Pages Scanned Pages Moved Pages Removed

------------- ----------- -------------

359           346         8

(1 row(s) affected)

DBCC dbreindex
:Generate one or more indexes for the tables in the specified database.

For example, DBCC dbreindex
('Taobaoitem _ 000000', 'ix _ taobaoitem_0003 ')

DBCC shrinkdatabase
:Shrink the size of data files and log files in the specified database.

For example, DBCC shrinkdatabase
('Taobao ')

DBCC shrinkfile (TRANSACT-SQL ):Shrink the size of specified data or log files in the current database

For example, DBCC shrinkfile
('Taobao3 ')

DBCC freeproccache:Deletes all elements from the process cache.

DBCC updateusage:Report and correct the page number and number of rows in the directory view. These errors may cause
Sp_spaceusedThe system stored procedure returns an incorrect space usage report.

Miscellaneous statements

DBCC dllname(Free): uploads the specified extended stored procedure DLL from the memory.

DBCC help: Returns the syntax information of the specified DBCC command.

For example, DBCC help
('Checkdb ')

DBCC freesessioncache:Refresh the Distributed Query connection cache used for Distributed queries executed on Microsoft SQL Server instances.

DBCC traceon
:Enable the specified trace tag.

Format: DBCC traceon (trace # [,... n] [,-1]) [with no_infomsgs]

DBCC traceoff
:Disable the specified trace tag.

DBCC freesystemcache:Release all unused cache entries from all caches. SQL Server 2005 database engine clears unused cache entries in the background in advance so that the memory can be used for the current entry. However, you can use this command to manually delete unused entries from all caches.

For example, DBCC freesystemcache ('all ')