DBCC commands required by the SQL Server database administrator

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}

)

[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}]

}

)

[With no_infomsgs]

DBCC freeproccache. Deletes all elements from the process cache. DBCC freeproccache [with no_infomsgs]

DBCC updateusage reports and corrects errors on the number of pages and number of rows in the directory view.

DBCC updateusage

({'Database _ name' | database_id | 0}

[, {'Table _ name' | table_id | 'view _ name' | view_id}

[, {'Index _ name' | index_id}]

) [With [no_infomsgs] [,] [count_rows]

]

Iii. 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]

}

]

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]

)

]

[

{

[All_errormsgs]

[No_infomsgs]

[, [Tablock]

[, [Estimateonly]

}

]

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}

]

)

[

{[All_errormsgs]

[, [No_infomsgs]

[, [Tablock]

[, [Estimateonly]

[, [Physical_only]

}

]

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]

)]

[

{

[, [All_indexes]

[, [Tableresults]

[, [Fast]

[, [All_levels]

[No_infomsgs]

}

]

DBCC opentdbcc inputbufferran: if the specified database contains the earliest active transactions and the earliest distributed and non-distributed replication transactions, the related information is displayed.

DBCC opentran

[

(['Database _ name' | database_id | 0])]

{[With tableresults]

[, [No_infomsgs]

}

]

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. Return the current output buffer of the specified session_id in hexadecimal and ASCII format. DBCC outputbuffer (session_id [, request_id])

DBCC tracestatus. The status of the trace flag is displayed. 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 errlog

Initialize SQL error logs

DBCC Buffer

Display the buffer header and page information

DBCC flushprocindb

Clear the cache content of a database stored procedure in the memory of the database server.

DBCC dbinfo

Display database result information

DBCC datable

Displays the table information for database management.

DBC ind

View the page information used by an index.

DBCC rebuldlog

Rebuild the SQL database transaction log file.

DBCC log

View the transaction log information of a Database

DBCC page

View the data of a Database

DBCC procbuf

Displays the buffer headers and stored procedures of the Process buffer pool.

DBCC prtipage

View the page number pointed to by each row on an index page.

DBCC PSS

Displays the process information that is currently connected to the sqlserver server.

DBCC Resource

Displays the resources currently used by the server.

DBCC Tab

View the structure of the data page.