DBCC COMMON command small sinks

DBCC is a set of console commands provided by SQL Server that are powerful enough to master some of the necessary statements to help with the operation of the database, so decided to tidy up, found that there are many similar to the collation, reduced a lot of work, categorized as follows:

One, DBCC help class command

* DBCC help ('? ')
Querying all DBCC commands
* DBCC help (' checktable ')
Query syntax description for the specified DBCC command
* DBCC Useroptions
Returns the SET options for the active (set) of the current connection

Second, DBCC Check validation class command

* DBCC checkalloc (' Database name ')
Checks the consistency of the disk space allocation structure for the specified database
* DBCC checkcatalog (' Database name ')
Check consistency between system tables and system tables in the specified database
* DBCC checkconstaints (' tablename ')
Checks the integrity of a specified constraint 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 structural integrity of all tables in the specified filegroup in the current database
* DBCC checktable
Checks the integrity of data, indexes, and test, ntest, and image pages for a specified table or indexed view
* DBCC Checkident
Checks the specified current identity value
* DBCC SQLPERF (umsstats) undocumented in BOL
Can be used to check if CPU usage reaches bottlenecks
One of the most critical reference data num runnable, indicating how many threads are currently waiting to run
If greater than or equal to 2, consider the CPU reaching the bottleneck

Iii. DBCC Maintenance class commands

* DBCC cleantable (' db_name ', ' table_name ')
Reclaim ALTER TABLE DROP COLUMN statement to delete variable-length columns or text
* DBCC Dbreindex
Rebuilds one or more indexes of a specified database
* DBCC Indexdefrag
Defragment indexes and nonclustered indexes on a table or view
* DBCC pintable (db_id,object_id)
To reside table data in memory
The way to see which tables reside in memory is:
Select ObjectProperty (object_id (' tablename '), ' tableispinned ')
* DBCC unpintable (db_id,object_id)
Undoing a table that resides in memory
* DBCC Shrinkdatabase (db_id,int)
Shrink the data file and log file size for the specified database
* DBCC Shrinkfile (file_name,int)
Shrink the specified data file and log file size for the related database

Iv. DBCC Performance Tuning commands

* DBCC dllname (free)
Sp_helpextendedproc viewing the loaded extension proc
Uninstalls the specified extension process in memory dynamic-link library (DLL)
* DBCC Dropcleanbuffers
Remove all buffers from the buffer pool
* DBCC Freeproccache
Remove all elements from the procedure buffer
* DBCC InputBuffer
Displays the last statement sent from the client to the server
* DBCC Opentran (db_name)
Queries a database for the longest execution of a transaction, by which program owns
* DBCC Show_statistics
Displays the current distribution statistics for the specified target on the specified table
* DBCC Showcontig
Displays fragmentation information for data and indexes for the specified table
* DBCC SQLPERF
(logspace) View log conditions for each DB
(iostats) View IO status
(threads) View Thread consumption
Returns a variety of useful statistics
* DBCC Cachestats
Display statistics for SQL Server 2000 memory
* DBCC Cursorstats
displaying statistics for SQL Server 2000 cursors
* DBCC Memorystats
Shows how SQL Server 2000 memory is broken down
* DBCC Sqlmgrstats
Displays SQL statements for pre-read and read-ahead in buffering

V. Non-disclosure of DBCC commands

* DBCC ErrLog
Initializing the error log file for SQL Server 2000
* DBCC flushprocindb (db_id)
Clears the stored procedure cache contents of a database in SQL Server 2000 Server memory
* DBCC Buffer (db_name,object_name,int (number of buffers))
Displays the header and page information for the buffer
* DBCC DBINFO (db_name)
To display the structure information for a database
* DBCC dbtable
Display table (data dictionary) information for managing data
* DBCC IND (db_name,table_name,index_id)
View page information used by an index
* DBCC Rebuildlog
Rebuilding SQL Server 2000 transaction log files
* DBCC LOG (db_name,3) ( -1--4)
View the transaction log information used by a database
* DBCC PAGE
View a Database data page information
* DBCC Procbuf
Displaying buffer headers and stored procedure headers in the process buffer pool
* DBCC Prtipage
View the page number that each line of an index page points to
* DBCC PSS (user,spid,1)
Displays process information that is currently connected to a SQL Server 2000 Server
* DBCC RESOURCE
Displays the current resource usage of the server
* DBCC TAB (db_id,object_id)
Show the structure of the data page

Vi. DBCC TRACE flag

Trace flags are used to temporarily set specific characteristics of a server or to turn off specific behavior, often for diagnosing performance problems or debugging stored procedures or complex computer systems
* DBCC TRACEON (3604)
Turn on trace flags
* DBCC Traceoff
Turn off trace flags
* DBCC Tracestats
View trace Flag Status

Vii. using DBCC result set output

Many DBCC commands can produce output in tabular format (using the WITH TABLERESULTS option). This information can be loaded into a table for future use. A sample script is shown below:

CREATE TABLE Dbccresult (

Dbccflag INT,

Result INT

)

INSERT into Dbccresult

EXEC (' DBCC tracestatus ( -1) with No_infomsgs ')

SELECT *

From Dbccresult
VIII. official use of DBCC recommendations
1. Run CHECKDB when the system usage rate is low.
2. Make sure that you do not perform other disk I/O operations at the same time, such as disk backup.
3. Place tempdb in a separate disk system or fast disk subsystem.
4. Allow tempdb to have sufficient expansion space on the drive. Use DBCC with ESTIMATE only
Estimate how much space will be required for tempdb.
5. Avoid running large CPU-intensive queries or batch jobs.
6. Reduce the active transaction while the DBCC command is running.
7. Using the NO_INFOMSGS option significantly reduces processing and the use of tempdb.
8. Consider using DBCC CHECKDB with PHYSICAL_ONLY option to check the page and record header
's physical structure. This operation performs a quick check when the hardware-led error is questioned.

You can do this when you want to use the server real name when subscribing to replication:

SELECT * from sysservers (the name of the original server can be found)

exec sp_dropserver ' jmsql9 ' (remove the original server name)

exec sp_addserver ' jmSQL9 ', LOCAL (changed to 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 on this table.

ALTER DATABASE [JM] SET Multi_user (changed to multi-user mode)

Repair_allow_data_loss: Performs all fixes that are done by repair_rebuild, including assigning and reassigning rows and pages to correct assignment errors, structural rows or page errors, and deleting corrupted text objects. These fixes can cause some data loss. The repair operation can be completed under a user transaction to allow the user to roll back the changes. If the repair is rolled back, the database will still contain errors and should be recovered from the backup. If a bug's fix is omitted due to the level of repair provided, any fix that depends on the fix will be omitted. After the repair is complete, back up the database.

Repair_fast perform small, time-consuming repair operations, such as repairing additional keys in a nonclustered index. These fixes can be done quickly and there is no risk of losing data.

Repair_rebuild performs all the repairs done by repair_fast, including repairs that take a long time, such as rebuilding the index, without the risk of data loss when performing these repairs.

DBCC SHRINKDATABASE (JM) Compression database

DBCC COMMON command small sinks