DBCC command

Transact-SQL programming language provides DBCC statements as Microsoft ® SQL Server ™ 2000 database console commands. These statements are physical and logical consistency of the database to be checked. Many DBCC statements can fix the problems detected.

Database Console Commands statement is divided into the following categories.

Statement classified perform the maintenance statement to the database, index or file group maintenance tasks.
Miscellaneous statement miscellaneous tasks such as enabling row-level locking or dynamic-link library (DLL) deleted from memory.
The status statement status check.
Verify the operation of the verification statement on the database, table, index, catalog, filegroup, system tables, or allocation of database pages.

SQL Server 2000 DBCC statement uses the input parameters and return values. All DBCC statement parameter can accept Unicode and DBCS literal.

DBCC result set output DBCC command can generate a table format output (use the WITH TABLERESULTS option). This information can be loaded into the table for future use. The following shows a sample script:

– Create the table to accept the results
CREATE TABLE # TRACESTATUS (
TraceFlag INT,
Status INT
)

– Execute the command, putting the results in the table
INSERT INTO # tracestatus
EXEC (‘DBCC TRACESTATUS (-1) WITH NO_INFOMSGS’)

– Display the results
SELECT *
FROM # TRACESTATUS
GO

Maintenance statement
The DBCC DBREINDEX – rebuild the specified database table one or more indexes.

DBCC DBREPAIR – remove damaged database.

DBCC INDEXDEFRAG – finishing the specified table or view clustered index and secondary index fragmentation.

DBCC SHRINKDATABASE – to shrink to specify the size of the data in the database file.

DBCC SHRINKFILE – shrink database specified data file or log file size.

DBCC UPDATEUSAGE – report and correct the sysindexes table is not correct the content, by the sp_spaceused system stored procedure may cause incorrect space usage statements.

Miscellaneous statement
DBCC dllname (FREE)

DBCC HELP

DBCC PINTABLE

DBCC ROWLOCK is

DBCC TRACEOFF

DBCC TRACEON

DBCC UNPINTABLE

Status statement
DBCC PINTABLE

DBCC OPENTRAN

DBCC OUTPUTBUFFER

DBCC PROCCACHE is

DBCC SHOWCONTIG

DBCC SHOW_STATISTICS

DBCC SQLPERF

DBCC TRACESTATUS

DBCC USEROPTIONS

Verification statement
DBCC CHECKALLOC

DBCC CHECKCATALOG

DBCC CHECKCONSTRAINTS

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKIDENT

DBCC CHECKTABLE

DBCC NEWALLOC

———————————— The following sections reprint ——— ————————————–

DBCC SQL Server provides a set of console commands, very powerful, and have some of the necessary statements, a lot of help on the operation of the database, so I decided to tidy up and found that there are many similar finishing, reducing a lot of work, categorized as follows:

DBCC help command

* DBCC HELP (‘?’)
Check all the DBCC command
* DBCC HELP (the ‘checktable’)
The query specified DBCC command syntax Description
* DBCC USEROPTIONS for
Returns the currently connected activities (setting) SET options

DBCC check validation class command

* DBCC CHECKALLOG (‘database name’)
Check the specified database consistency of disk space allocation structures
* DBCC CHECKCATALOG (‘database name’)
Consistency checks the specified database’s system tables and system tables
* DBCC CHECKCONSTAINTS (‘tablename’)
The integrity of the check on the specified table specified constraint or all constraints
* DBCC CHECKDB
Allocation and structural integrity of all objects in the inspection database
* DBCC CHECKFILEGROUP for
All tables in the current database checks the specified file group allocation and structural integrity
* DBCC CHECKTABLE
Checks the specified table or indexed view data, index, and test, nTest and image pages for the integrity of the
* DBCC CHECKIDENT
Check the specified current identity value
* DBCC SQLPERF (UMSSTATS) undocumented in BOL
Can be used to check whether the CPU usage of the most critical bottlenecks reference data num runnable, indicating that the current number of threads waiting to run if it is greater than or equal to 2, consider the CPU to bottleneck

DBCC maintenance commands

* DBCC CLEANTABLE (‘db_name’, ‘table_name’)
Recycling Alter table drop column statement to delete variable-length columns or text
* DBCC DBREINDEX
One or more indexes rebuild the specified database
* DBCC INDEXDEFRAG
Defragment the index and non-clustered index on the table or view
* DBCC PINTABLE (db_id, object_id)
View the table data resides in memory which tables reside in the memory are:
select objectproperty (object_id (‘tablename’), ‘tableispinned’)
* DBCC UNPINTABLE (db_id, object_id)
Undo the table resides in memory
* DBCC SHRINKDATABASE (db_id int,)
Contraction of the specified database data files and log file size
* DBCC SHRINKFILE (file_name, int)
Contraction database specified data files and log file size

DBCC performance adjustment commands

* DBCC dllname (FREE)
the loaded extension sp_helpextendedproc view PROC
Unloads the specified memory expansion process dynamic link library (DLL)
* DBCC DROPCLEANBUFFERS for
All buffer is removed from the pool
* DBCC FREEPROCCACHE
Remove all elements from the process buffer
* DBCC INPUTBUFFER for
Display last statement sent from the client to the server
* DBCC OPENTRAN (db_name)
For longest execution time of a database transaction, which has
* DBCC SHOW_STATISTICS
The display on the specified table specified target distribution statistics
* DBCC SHOWCONTIG
Display the data and indexes of the specified table fragmentation information
* DBCC SQLPERF for
(LOGSPACE) DB log
(Iostats) View IO
(Threads) thread consumption returns a variety of useful statistical information
* DBCC CacheStats for
SQL Server 2000 memory statistics
* DBCC CURSORSTATS for
To display cursor SQL Server 2000 statistics
* DBCC MemoryStats for
SQL Server 2000 how memory is subdivided
* DBCC SQLMGRSTATS for
Display buffer in the first reading and pre-reading prepared SQL statement

, DBCC command undisclosed

* DBCC ERRLOG for
The initialization SQL Server 2000 error log file
* The DBCC FLUSHPROCINDB (DB_ID)
Clear a database stored procedure in SQL Server 2000 server memory cache content
* DBCC buffer (db_name, object_name, int (number of buffers))
The buffer header information and page information
* DBCC dbinfo (db_name)
Display the structure of the database information
* DBCC DBTABLE
Display management data table (data dictionary)
* DBCC IND (db_name, table_name, index_id)
View an index page
* DBCC REBUILDLOG for
Reconstruction of SQL Server 2000 transaction log files
* DBCC LOG (db_name, 3) (-1 – 4)
View a database transaction log information
* DBCC PAGE
A database data page information
* DBCC PROCBUF for
Display process buffer pool buffer head and stored procedures head
* DBCC PRTIPAGE for
View an index page per line pointing to the page number
* DBCC PSS (user, spid, 1)
Currently connected to the SQL Server 2000 server process information is displayed
* DBCC RESOURCE
Display server is currently using resources
* DBCC TAB (the DB_ID, object_id)
The structure of the display data page

, DBCC trace flag

Trace flags are used to temporarily set specific server characteristics or turn off specific behavior, commonly used in the diagnosis of performance problems or debug stored procedures or complex computer systems
* DBCC TRACEON (3604)
Open the trace flag
* DBCC traceoff
Close trace flag
* DBCC TRACESTATS for
View the trace flag state

Use DBCC result set output

Many DBCC command can generate a table format output (use the WITH TABLERESULTS option). This information can be loaded into the table for future use. The following shows a sample script:

The CREATE TABLE DBCCResult (

DBCCFlag INT

Result INT

)

INSERT INTO DBCCResult

EXEC (‘DBCC TRACESTATUS (-1) WITH NO_INFOMSGS’)

SELECT *

FROM DBCCResult
Eight official use DBCC
1, when system usage is low running CHECKDB.
2, make sure that are not performing other disk I / O operations, such as disk backup.
3, the tempdb on a separate disk system or fast disk subsystem.
4, allowing tempdb on the drive there is enough room for expansion. DBCC with ESTIMATE ONLY
The estimated amount of tempdb space required for entry.
5, to avoid running CPU-intensive queries or batch jobs.
6, run the DBCC command to reduce the active transaction.
7 use NO_INFOMSGS options significantly reduce the processing and use of tempdb.
8, consider using with PHYSICAL_ONLY options DBCC CHECKDB to check the page and record the physical structure of the header. When hardware errors caused doubt, this operation will perform a quick check.

Posted Subscribe to copy, use server name can do:

select * from sysservers (you can find the original name of the server)

exec sp_dropserver ‘jmsql9’ (delete the original server name)

exec sp_addserver ‘jmSQL9’, LOCAL (changed to the new server name)

ALTER DATABASE [jm] SET SINGLE_USER (changed to single-user mode)

DBCC CHECKDB (“DatabaseName”, REPAIR_REBUILD) WITH tablock (repair the database)

DBCC CHECKTABLE (“tablename”, repair_rebuild) with tablock (repair table)

DBCC DBREINDEX (the ‘t_icitem’, ”), fix the table index.

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

REPAIR_ALLOW_DATA_LOSS: executed by REPAIR_REBUILD, completed repair, including the allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can cause some data loss. The repair can be completed in under a user transaction to allow users to roll back the changes. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If for the sake of the provided repair level omission of an error repair, will be missing any repair depends on the repair. After repairs are completed, back up the database.

REPAIR_FAST small-consuming repair actions such as repairing extra keys in the non-clustered index. These repairs can be done quickly and there will be no risk of losing data.

REPAIR_REBUILD to perform completed by REPAIR_FAST, all repair, including the need for time-consuming repairs (such as rebuilding indexes), the implementation of these fixes there will be no risk of losing data.

DBCC SHRINKDATABASE (JM) compression database