The collected full DBCC solution, including those not listed in the document

DBCC: http://msdn2.microsoft.com/zh-cn/library/ms188796.aspx on msdn

1. Understand DBCC
DBCC (DBCC) is a practical command set used to check the logical and physical consistency of databases.
Database Console Command statements can be divided into the following categories:
Maintenance: a task for maintaining 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.
Verification: verification of database, table, index, directory, file group, or database page allocation

2. dbbcc maintenance statement: a task for maintaining databases, indexes, or file groups.
DBCC cleantable. Reclaim the space of the deleted variable-length columns and text columns.
DBCC cleantable
(
{'database _ name' | database_id | 0}
, {'table _ name' | table_id | 'view _ name' | view_id}
[, batch_size]
)
[with no_infomsgs]
DBCC indexdefrag. Specify the index fragmentation for the table or view.
DBCC indexdefrag
(
{'database _ name' | database_id | 0}
, {'table _ name' | table_id | 'view _ name' | view_id}
,{ 'index _ name' | index_id}
, {partition_number | 0 }< br>)
[with no_infomsgs]

DBCC dbreindex. Generate one or more indexes for the tables in the specified database.
DBCC dbreindex
(
'Table _ name'
[, 'Index _ name' [, fillfactor]
)
[With no_infomsgs]

DBCC shrinkdatabase. Shrink the data file size in the specified database.
DBCC shrinkdatabase
('Database _ name' | database_id | 0
[, Target_percent]
[, {Notruncate | truncateonly}]
)
[With no_infomsgs]

DBCC dropcleanbuffers. Delete all cleared buffers from the buffer pool. DBCC dropcleanbuffers [with no_infomsgs]
DBCC shrinkfile. Shrink the size of the specified data file or log file of the relevant database.
DBCC shrinkfile
(
{'file _ name' | file_id}
{[, emptyfile]
| [[, target_size] [, {notruncate | truncateonly}]
}< br>)
[with no_infomsgs]
DBCC freeproccache. Deletes all elements from the process cache. DBCC freeproccache [with no_infomsgs]
DBCC updateusage reports the page number and row number errors in the directory view and corrects them.
DBCC updateusage
({'database _ name' | database_id | 0}
[, {'table _ name' | table_id | 'view _ name' | view_id}
[, {'index _ name' | index_id}]
) [With [no_infomsgs] [,] [count_rows]
]

3. dbbcc verification statement: verifies the distribution of databases, tables, indexes, directories, file groups, or database pages.
DBCC checkalloc. Check whether the disk space allocation structure of the specified database is consistent.
DBCC checkalloc
[
(
['database _ name' | database_id | 0]
[, noindex
|
{repair_allow_data_loss
| repair_fast
| repair_rebuild
}]
)
]
[with {[all_errormsgs]
[, no_infomsgs]
[, tablock]
[, estimateonly]
}< br>]

DBCC checkfilegroup. Checks the distribution and structure integrity of all tables in the specified file group in the current database.
DBCC checkfilegroup
[
(
[{'filegroup _ name' | filegroup_id | 0}]
[, noindex]
)
]
[with
{< br> [all_errormsgs]
[no_infomsgs]
[, [tablock]
[, [estimateonly]
}< br>]
DBCC checkcatalog. Check the directory consistency in the specified database. The database must be online.
DBCC checkcatalog
[
(
'database _ name' | database_id | 0
)
]
[with no_infomsgs]

DBCC checkident. Check the current Identifier value of the specified table. If necessary, change the Identifier value.
DBCC checkident
(
'Table _ name'
[,{
Noreseed | {reseed [, new_reseed_value]}
}
]
)
[With no_infomsgs]

DBCC checkconstraints. Checks the integrity of the specified or all constraints on the specified table in the current database.
DBCC checkconstraints
[
(
'Table _ name' | table_id | 'constraint _ name' | constraint_id
)
]
[
{All_constraints | all_errormsgs} [, no_infomsgs]
]

DBCC checktable. Check the integrity of all pages and structures in a table or index view.
DBCC checktable
(
'table _ name' | 'view _ name'
[, noindex
| index_id
| {repair_allow_data_loss
| repair_fast
| repair_rebuild}
]
)
[with
{[all_errormsgs]
[, [no_infomsgs]
[, [tablock]
[, [estimateonly]
[, [physical_only]
}< br>]

DBCC checkdb. Checks the distribution, structure, and logical integrity of all objects in the specified database.
DBCC checkdb
[
(
'Database _ name' | database_id | 0
[, Noindex
| {Repair_allow_data_loss
| Repair_fast
| Repair_rebuild
}]
)
]
[{
[All_errormsgs]
[, [No_infomsgs]
[, [Tablock]
[, [Estimateonly]
[, [Physical_only] | [, [data_purity]
}
]

Iv. dbbcc information statements

DBCC show_statistics. Displays the current distribution statistics of the specified target in the specified table.
DBCC inputbuffer. displays the last statement sent from the client to the Microsoft SQL Server 2005 instance. DBCC inputbuffer (session_id [, request_id]) [with no_infomsgs]
DBCC showcontig. displays the data and index fragmentation information of the specified table.
DBCC showcontig
[(
{'table _ name' | table_id | 'view _ name' | view_id}
[, 'Index _ name' | index_id]
)]
[with
{
[, [all_indexes]
[, [tableresults]
[, [Fast]
[, [all_levels]
[no_infomsgs]
}< br>]

If DBCC opentdbcc inputbufferran contains the earliest active transactions and the earliest distributed and non-distributed replication transactions in the specified database, the related information is displayed.
DBCC opentran
[
(['database _ name' | database_id | 0])]
{[with tableresults]
[, [no_infomsgs]
}< br>]
DBCC sqlperf. provides statistics on how to use the transaction log space in all databases.
DBCC sqlperf (logspace | 'sys. dm_ OS _latch_stats ', clear | 'sys. dm_ OS _wait_stats ', clear)
[with no_infomsgs]
DBCC outputbuffer. returns the current output buffer of the specified session_id in hexadecimal and ASCII format. DBCC outputbuffer (session_id [, request_id])
DBCC tracestatus. displays the status of the trace flag. DBCC tracestatus ([[trace #[,... n] [,] [-1])
DBCC proccache. process cache information is displayed in table format. DBCC proccache [with no_infomsgs]
DBCC useroptions returns the set Option of the current connection activity (setting. DBCC useroptions

V. Miscellaneous statements of dbbcc: miscellaneous tasks, such as enabling trace flag or deleting DLL from memory.

DBCC help. Returns the syntax information of the specified DBCC command. DBCC help ('dbcc _ statement '| @ dbcc_statement_var | '? ') [With no_infomsgs]
DBCC dllname (free ). Upload the specified extended stored procedure DLL from the memory. DBCC dllname (free) [with no_infomsgs]
DBCC dbrepair. Disable the specified trace tag. DBCC traceoff (trace # [,... n] [,-1]) [with no_infomsgs]
DBCC traceon. Enable the specified trace tag. DBCC traceon (trace # [,... n] [,-1]) [with no_infomsgs]

6. undisclosed DBCC
DBCC cachestats: displays information about objects in the current buffer cache, such as hit rates, compiled objects, and execution plans.

Example: DBCC cachestats execution result (scaling down) Object Name hit ratio
------------ ------------- Proc 0.86420054765378507
Preparted 0.99988494930394334
Adhoc 0.93237136647793051
Replproc 0.0
Trigger 0.99843452831887947
Cursor 0.42319205924058612.
Exec CX 0.65279111666076906
View 0.95740334726893905
Default 0.60895011346896522
Usrtab 0.94985969576133511.
Recall AB 0.0
Check0.67021276595744683
Rule 0.0.
Summary 0.80056155581812771 Some key statistics can be obtained from this command: L Hit ratio : Displays the percentage that a specific object can be hit in the SQL Server cache. The larger the value, the better. L Object count : Displays the total number of hits of specific types of objects in the SQL Server cache. L AVG. Cost : SQL Server is used to measure the time required to compile an execution plan and the memory required for this plan. Based on this value, you can determine whether the execution plan should be loaded into the cache. L AVG. Pages : Measure the average total number of objects in the cache using 8 K pages L LW ojbect count , Lw avg cost , Wl avg stay , LW Ave use : The values of these columns indicate how many specific objects have been removed from the cache by the write process. The lower the value, the better. 2 . DBCC dropcleanbuffers : Delete all from the buffer pool to clear the buffer pool. During the test, you can use this command to clear all test data from the SQL server's data cache (buffer) to ensure the fairness of the test. Note that this command only removes the clean cache and does not remove the dirty cache. For this reason, before executing this command, you should first execute the checkpoint to write all the dirty caches to the disk. In this way, when running DBCC rropcleanbuffers, all data caches can be cleared, it is not a part of it. 3 . DBCC errorlog : If the MSSQLServer service is rarely restarted, the server logs will grow fast, and the speed of opening and viewing logs will also be slow. This command can be used to truncate the current server log, mainly to generate a new log. You can set a scheduling task and execute this command every week to automatically truncate server logs. Use stored procedures Sp_cycle_errorlog You can also achieve the same purpose. 4 . DBCC flushprocindb: Clears the cache used by the stored procedure of a specified database in a database instance. Database ID Is a mandatory parameter. This stored procedure can be used to ensure that the previous Stored Procedure plan does not negatively affect the test results during testing. Example: Declare @ intdbid Integer Set @ intdbid = (select dbid from Master. DBO. sysdatabases where name = 'database _ name ')
DBCC flushprocindb (@ intdbid) 5 . DBCC freeproccache : Cache is used to clean up all databases. For example, during the release process, the cache will re-compile some parts (such as special SQL statements), rather than re-use them from the cache. 6 . DBCC memorystatus : Lists A detailed category, which shows how the SQL Server cache is allocated, including cache activity. 7 . DBCC page : For example, DBCC page (dbid | dbname), pagenum [, print option] [, cache] [, logical]) parameter description: dbid or dbname: it can be a database ID or database name. Pagenum: Print option of the page number to be checked: (optional) the value of the print option is 0, 1, 2. 0- (Default) display page header information 1- Displays the header information of the page, the information of each row on the page, and the Offset Table of the page. Lines on the page are displayed one by one. 2- Similar to option 1, a single information block is displayed instead of a page row by row. Cache: (optional) the value of this parameter is 1 or 0, 0-Command to query the page number directly from the disk, instead of checking whether the page number is in the cache DBCC page 1- If a page is in the cache, the page is obtained from the cache first, rather than directly from the disk. Logical: (optional) this parameter is used to obtain the page number from a virtual page or a logical page. The value of this parameter can be 1 or 0, 0- A virtual page number 1- A logical page number 8 . DBCC sqlmgrstats : These values are used to generate three different values. They are used in the ad-hoc and pre-compiled tsql statements you want to view how the cache works. Example: DBCC sqlmgrstats Result: item status
------------------------------------
Memory Used (8 K pages) 5446
Number csql objects 29098
Number false hits 425490 where: L Memor used (8 K pages): if the number of memory pages is very large, this may be a prompt: Some user connections are preprocessing many tsql statements. L Number csql objects: indicates the total number of tsql statements in the cache. L Number false hits: Sometimes, when the SQL Server matches a tsql statement that already exists in the cache, an error occurs. Ideally, this number should be as small as possible. 8 . DBCC sqlperf () : This command includes the options with instructions and no instructions. DBCC sqlperf ( Logspace ) Provides statistics on the usage of transaction log space in all databases. For more information, see online help. DBCC sqlperf (umsstats): provides statistics about SQL Server thread management. The result is as follows: Statistic Value -------------------------------- ------------------------ Scheduler ID 0.0 Num users 17.0 Num runnable 0.0 Num workers 13.0 Idle workers 6.0 Work queued 0.0 Cntxt Switches 76752.0 Cntxt switches (idle) 47139.0 Scheduler Switches 0.0 Total work 54056.0 The following are some key statistical information explanations: L Scheduler ID: Each CPU corresponds to a Scheduling Program This is the serial number of the Scheduler L Num User: Number of SQL Server threads in the current scheduling queue L Num runnable: Number of SQL Server threads currently running L Num workers: thread pool size L Idle workers: idle workers. L Cntxt switches: Number of exchange contexts between executable threads DBCC sqlperf (waitstats): provides information about SQL Server Read-ahead Activity Information DBCC sqloerf ( Iostats ): Provides the main SQL Server Read and Write Information DBCC sqlperf ( Rastats ): Provide SQL Server read-ahead Activity information DBCC sqlperf ( Threads ): Provides each SQL Server Thread Of I/O , CPU And memory usage information.