SQL Server DBCC usage

Msdn: http://msdn.microsoft.com/zh-cn/library/ms188796.aspx

DBCC is a set of Console Commands provided by SQL Server. It has powerful functions and has some necessary statements, which is helpful for database operations. Therefore, we decided to sort it out and found many similar commands, A lot of work is reduced, which is classified as follows:

1. DBCC help commands

* DBCC help ('? ')
Query all DBCC commands
* DBCC help ('COMMAND ')
Query the syntax of the specified DBCC command
* DBCC useroptions
Returns the set Option of the current connection activity (setting ).

2. DBCC check and verification commands

* DBCC checkallog ('database name ')
Check whether the disk space allocation structure of the specified database is consistent.
* DBCC checkcatalog ('database name ')
Check the consistency between the system table and the system table of the specified database.
* DBCC checkconstaints ('tablename ')
Checks the integrity of specified or all constraints on a specified table.
* DBCC checkdb
Check the allocation and structural integrity of all objects in the database
* DBCC checkfilegroup
Checks the allocation and structure integrity of all tables in the specified file group in the current database.
* DBCC checktable
Checks the integrity of data, indexes, and test, ntest, and image pages of a specified table or index view.
* DBCC checkident
Check the specified current ID value
* DBCC sqlperf (umsstats) unmarshented in BOL
It can be used to check whether the CPU usage reaches the bottleneck.
The most critical reference data is num runnable, which indicates how many threads are waiting to run.
If the value is greater than or equal to 2, the CPU reaches the bottleneck.

Iii. DBCC maintenance commands

* DBCC cleantable ('db _ name', 'table _ name ')
Revoke alter table drop column statement to delete variable length columns or text
* DBCC dbreindex
Rebuild one or more indexes of a specified database
* DBCC indexdefrag
Defragmentation of indexes and non-clustered indexes on tables or views
* DBCC pintable (db_id, object_id)
Resident table data in memory
To view which tables reside in the memory, follow these steps:
Select objectproperty (object_id ('tablename'), 'tableispinned ')
* DBCC unpintable (db_id, object_id)
Undo a table residing in the memory
* DBCC shrinkdatabase (db_id, INT)
Shrink data files and log files of a specified database
* DBCC shrinkfile (file_name, INT)
Shrink the specified data file and Log File Size of the relevant database

Iv. DBCC performance Adjustment Command

* DBCC dllname (free)
Sp_helpextendedproc view the loaded extension proc
Detach a specified dynamic link library (DLL) for the extension process in the memory)
* DBCC dropcleanbuffers
Delete all buffers from the buffer pool
* DBCC freeproccache
Delete all elements from the process Buffer
* DBCC inputbuffer
Display the last statement sent from the client to the server
* DBCC opentran (db_name)
Queries which program owns the transactions with the longest execution time of a Database
* DBCC show_statistics
Displays the current distribution statistics of the specified target in the specified table.
* DBCC showcontig
Displays the data and index fragmentation information of a specified table.
* DBCC sqlperf
(Logspace) view the logs of each database
(Iostats) view Io status
(Threads) view thread consumption
Returns a variety of useful statistics.
* DBCC cachestats
Displays statistics of SQL Server 2000 memory
* DBCC cursorstats
Displays SQL Server 2000 cursor statistics
* DBCC memorystats
Show how SQL Server 2000 memory segments
* DBCC sqlmgrstats
Displays the SQL statements prepared for buffer first read and pre-read

5. commands not made public by DBCC

* DBCC errlog
Initialize the Error Log File of SQL Server 2000
* DBCC flushprocindb (db_id)
Clears the Stored Procedure cache content of a database in SQL Server 2000 Server Memory.
* DBCC Buffer (db_name, object_name, INT (number of buffers ))
Display the buffer header information and page information
* DBCC dbinfo (db_name)
Display database structure information
* DBCC dbtable
Displays the table (data dictionary) information for managing data.
* DBCC Ind (db_name, table_name, index_id)
View the page information used by an index
* DBCC rebuildlog
Rebuilding the SQL Server 2000 Transaction Log File
* DBCC log (db_name, 3) (-1--4)
View the transaction log information used by a database
* DBCC page
View information on a database data page
* DBCC procbuf
Displays the buffer and stored procedure headers in the process buffer pool.
* DBCC prtipage
View the page number pointed to by each row on an index page
* Dbcc pss (user, spid, 1)
Displays the process information that is currently connected to the SQL Server 2000 Server.
* DBCC Resource
Displays the resources currently used by the server.
* DBCC tab (db_id, object_id)
Display the structure of the data page

Vi. DBCC tracking mark

Trace flag is used to temporarily set specific characteristics of the server or disable specific behavior, often used to diagnose performance problems or debug stored procedures or complex computer systems
* DBCC traceon (3604)
Open a trail tag
* DBCC traceoff
Disable tracking tag
* DBCC tracestats
View trace tag status

7. Use DBCC result set output

Many DBCC commands can generate output in table format (using the with tableresults option ). This information can be loaded into the table for future use. The following shows an example script:

　　
Create Table dbccresult (

Dbccflag int,

Result int

)

Insert into dbccresult

Exec ('dbcc tracestatus (-1) with no_infomsgs ')

Select *

From dbccresult
8. Suggestions for using DBCC officially
1. Run checkdb when the system usage is low.
2. Make sure that other disk I/O operations, such as disk backup, are not performed at the same time.
3. Place tempdb in a separate disk system or fast disk subsystem.
4. Allow tempdb to have enough expansion space on the drive. Use DBCC with estimate only
Estimate the amount of space required by tempdb.
5. Avoid running query or batch processing jobs that occupy a large amount of CPU.
6. Reduce the number of active transactions when running the DBCC command.
7. Use the no_infomsgs option to significantly reduce processing and tempdb usage.
8. Consider using DBCC checkdb with physical_only option to check the page and record header.
. This operation performs a quick check when errors caused by hardware are in question.

 

 

 

 

 

When publishing and subscribing to replication, you can use the real name of the server as follows:

Select * From sysservers (you can find the name of the original server)

 

Exec sp_dropserver 'jmsql9' (delete the original server name)

 

Exec sp_addserver 'jmsql9', local (change to the new server name)

 

 

 

Alter database [JM] Set single_user (changed to single-user mode)

 

DBCC checkdb ("databasename", repair_rebuild) with tablock (repair database)

DBCC checktable ("tablename", repair_rebuild) with tablock (repair table)

DBCC dbreindex ('t_ icitem', '') fixes all indexes in the table.

 

Alter database [JM] Set multi_user (changed to multi-user mode)

 

Repair_allow_data_loss: executes all repairs completed by repair_rebuild, including allocating and unassigning rows and pages to correct allocation errors, structure row or page errors, and deleting corrupted text objects. These fixes may cause some data loss. The repair operation can be completed under the user transaction to allow the user to roll back the changes. If the rollback is fixed, the database will still contain errors and the backup should be restored. If an error fix is missing due to the severity of the severity level provided, it will omit any fix that depends on the fix. After the restoration, back up the database.

Repair_fast Performs small and time-consuming repair operations, such as fixing additional keys in non-clustered indexes. These repairs can be completed quickly without the risk of data loss.

Repair_rebuild executes all the repairs completed by repair_fast, including repairs that require a long period of time (such as re-indexing), so there is no risk of data loss when performing these repairs.