SQL SERVER DBCC command explanation

SQL SERVER DBCC command explanation

I got up at eight o'clock and got it all the time. I don't know much about it. I just put it up first and then slowly modify it.

-- 1 dbcc trraceon DBCC TRACEOFF

-- For database deadlocks, trace flag 1204, 1205, and 1206 can be used to check the output in ERRORLOG and analyze the execution context of SQLTRACE to determine the cause of the deadlock.

-- The third parameter of the TRACEON function is set to-1, indicating that it is not only for the current connection,

-- It is for all connections established in the future. In this way, it is enough, otherwise it is only monitoring the existing database connection.

-- Execute the following statement to record the deadlock to the Errorlog:

Dbcc traceon (1204,360 5,-1)

Go

Dbcc tracestatus (-1)

Go

-- Note:

-- 260: prints version information about the dynamic link library of the Extended Stored Procedure

-- 444: Stop auto-parameterization

-- 1200: Output lock Information

-- 1204: returns the running data of SQL SERVER-related programs involved in deadlocks.

-- 1211: Stop lock escalation (lock upgrade)

-- 1252: displays information about Dynamically selected locks.

-- 2528: Disable Parallel Object check through dbcc checkdb, dbcc checkfilegroup, and dbcc checktable.

-- By default, the degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured in the same way as the parallel query.

-- For more information, see the max degree of parallelism option.

-- Generally, parallel DBCC should be enabled. When dbcc checkdb is executed,

-- Query the processor to re-evaluate and automatically adjust the degree of parallelism, and check each table or batch of tables.

-- Sometimes, check may be performed when the server is actually idle. If the Administrator knows that the load will increase before the Check ends,

-- You may want to manually reduce or disable the degree of parallelism.

-- However, disabling parallel checks will reduce the overall performance of the database. Reducing the degree of parallelism increases the number of transaction logs that must be scanned.

-- This in turn increases the demand for tempdb space, and leads to a non-linear increase in the time required for dbcc to complete the check.

-- If the TABLOCK function is enabled and the degree of parallelism is disabled when DBCC is run, the table may be locked for a longer time.

-- 3205: by default, if the tape drive supports hardware compression, the DUMP or BACKUP statement uses this function.

-- This trace flag can be used to disable hardware compression for the tape driver.

-- This item is useful when you want to swap tape with other sites or tape drives that do not support compression.

-- 3604: Output trace results to the front-end

-- 3605: DBCC output is required to be placed in SQL server ERROR LOG.

-- 8602: Disable the index prompt Function

-- 8722: Stop join group and other optimization tips

-- 8755: Stop lock prompt Function

-- 8780: Stop the optimization timeout configuration and force the complete optimization action.

-- 2 DBCC page

Dbcc traceon (3604)

Dbcc page (northwind, 1,100, 1)

/* Query Information on the 101st pages of northwind data */

/* DBCC Page ({dbid | dbname}, filenum, pagenum [, printopt])

?

The specific parameters are described as follows:

Dbid: The Database ID that contains the page

Dbname: name of the database containing the page

Filenum: Number of the file containing the page

Pagenum: page in the file

Printopt: Optional output options; select one of the values:

0: default value. The title and page title of the output buffer.

1: output buffer title, page title (output each row separately), and row Offset Table

2: output buffer title, page title (overall output page), and row Offset Table

3: The title of the output buffer, the page title (each row is output separately), and the row Offset Table. Each row is followed by its column values listed separately.

*/

-- 3 DBCC checkalloc

DBCC checkalloc (northwind)

/* Check the system table and table consistency of the specified database.

Checkalloc is used to check the specified database and view all the correctly allocated pages and unallocated pages.

If no database name is specified, checkalloc checks the current database. Checkalloc returns the allocated and used space.

The default mode of checkalloc is nofix. To use the fix option, you must place the database in single-user mode.

*/

-- 4 DBCC checkcatalog

DBCC checkcatalog (northwind)

/*

Check the consistency between the system table and system table of the batch database.

*/

-- 5 DBCC checkconstraints

DBCC checkconstraints (products)

/*

Checks the integrity of specified or all constraints on a specified table.

DBCC CHECKCONSTRAINTS

[('Table _ name' | 'constraint _ name'

)]

[WITH {ALL_ERRORMSGS | ALL_CONSTRAINTS}]

Dbcc checkconstraints checks the consistency of certain or all constraints in a database.

Dbcc checkconstraints is always executed in the context of the current database.

Note that dbcc checkconstraints does not perform disk or file-level consistency detection;

It only ensures the consistency of foreign key definitions, and also checks the constraints-only to confirm that the data is valid.

If you want to check the consistency between the above table and the index on the disk,

You should execute dbcc checkdb or the combination of dbcc checkalloc and dbcc checktable on all tables.

*/

-- 6 DBCC checkdb

DBCC checkdb

/*

Check the allocation and structural integrity of all objects in the database

Checkdb [('database _ name' [, NOINDEX | REPAIR])]

[WITH NO_INFOMSGS [, ALL_ERRORMSGS] [, PHYSICAL_ONLY]

[, ESTIMATEONLY] [, TABLOCK]

*/

-- 7 DBCC cleantable

DBCC cleantable

/*

Reclaim the storage space after the alter table drop column statement deletes a variable-length column or text column

Cleantable ('database _ name' | database_id, 'table _ name' | table_id, [batch_size])

*/

-- 8 DBCC dbreindex

DBCC dbreindex

/*

Rebuild one or more indexes of a specified database

Dbreindex ('table _ name' [, index_name [, fillfactor]) [WITH NO_INFOMSGS]

*/

-- 9 DBCC indexdefrag

DBCC indexdefrag

/*

Defragmentation of indexes and non-clustered indexes on tables or views

Indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname })

*/

-- 10 DBCC pintable/DBCC unpintable

-- Resident the table data in the memory or revoke the data in the memory.

-- Pintable (database_id, table_id)

-- 11 DBCC shrinkdatabase

-- Shrink the data file and Log File Size of the specified database

-- Shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}])

-- 12 DBCC shrinkfile

-- Shrink the specified data file and Log File Size of the relevant database

-- Shrinkfile ({fileid | 'filename'}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}])

--

-- 13 DBCC dllname (free)

-- Sanction specified extensions in memory Miss process dynamic link library (DLL)

Sp_helpextended proc

-- Query the dynamic link library of the extended stored procedure in the current Memory

---

-- 14 DBCC dropcleanbuffers

-- Delete all buffers from the buffer pool

/*

 

Use dbcc dropcleanbuffers to test queries with a cold high-speed cache, instead of shutting down or restarting the server

*/

-----

-- 15 DBCC freeproccache

-- Delete all elements from the process Buffer

-- High-speed cache for clearing all databases

-------

-- 16 DBCC inputButter

-- Display the last statement sent from the client to the server

---

-- 17 DBCC opentran

-- Query which program owns the transaction with the longest execution time of a Database

------

-- 18 DBCC show_statistics

-- Displays the statistics of the specified current distribution on the specified table.

--------

-- 19 DBCC showcontig

-- Display the data and index fragmentation information of the specified table

-------

-- 20 DBCC sqlperf

-- Available parameters: logspace, iostats, and threads

-- Return multiple useful statistics

--- Dbcc sqlperf (logspace)

Database Name Log Size (MB) Log Space Used (%) Status

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

Master 5.0546875 27.93663 0

Tempdb 0.7421875 58.88158 0

Model 0.4921875 74.206352 0

Msdb 2.2421875 38.414635 0

Pubs 0.7421875 43.947369 0

Northwind 0.9921875 41.732285 0

Db40cdr 32.304688 6.3618503 0

Fcdb 218.30469 79.981796 0

Fcdb_20070826 19.992188 3.0138726 0

Test 176.92969 6.4423542 0

Kldb 0.9921875 33.267715 0

-- Dbcc sqlperf (iostats)

Statistic Value

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

Reads Outstanding 0.0

Writes Outstanding 0.0

-- Dbcc sqlperf (threads)

Spid Thread ID Status LoginName io cpu MemUsage

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

1 NULL background NULL 0 0 0

2 NULL background NULL 0 0 5

3 NULL sleeping NULL 0 0 0

4 NULL background NULL 0 0-6

5 0 background sa 41 0 3

6 NULL sleeping NULL 1 0 0

7 0 background sa 0 0 3

8 0 background sa 0 0 3

9 0 background sa 0 0 3

10 0 background sa 0 0 3

11 0 background sa 0 0 3

12 0 background sa 0 0 3

51 0 sleeping RD-001Adm 19 32 29

52 0 sleeping RD-001Adm 6 515 15

53 3828 runnable RD-001Adm 1 15 5

--------

-- 21 DBCC cachestats

-- Display statistics of SQL SERVER Memory

-------

-- 22 DBCC cursorstats

-- Displays statistics of SQL SERVER cursors.

--------

-- 23 DBCC sqlmgrstats

-- Displays the SQL statements read first and prepared in advance in the buffer.

--

-- 24 DBCC errlog

-- Initialize the SQL SERVER Error Log File

-- 25 DBCC flushprocindb

-- Clears the Stored Procedure cache content of a database in the memory of the SQL SERVER.

-- 26 DBCC Buffer

-- Display the good information and page information of the buffer zone

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

-- 27 DBCC DBinfo

-- Display database structure information

--

-- 28 DBCC DBtable

-- Display the table information of the management data

-

-- 29 DBCC IND

-- View the page information used by an index

---

-- 30 DBCC REbuild_log

-- Re-create the SQL SERVER Transaction Log File

--

-- 31 DBCC log

-- View the transaction log information used by a database

-

-- 32 DBCC procbuf

-- Display the buffer header and stored procedure header in the process Buffer Pool

--

-- 33 DBCC prtipage

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

--

-- 34 DBCC pss

-- Displays information about the processes currently connected to SQL SERVER.

-

-- 35 DBCC resource

-- Display the current resources used by the server

--

-- 36 DBCC tab

-- View the structure of the data page