Database Consistency Checker (dbcc) checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality.
dbcc checkalloc [(database_name [, fix | nofix])]
dbcc checkcatalog [(database_name)]
dbcc checkdb [(database_name [, skip_ncindex])]
dbcc checkstorage [(database_name)]
dbcc checktable({table_name | table_id}[, skip_ncindex])
dbcc checkverify [(database_name)]
dbcc complete_xact (xid, {"commit" | "rollback"})
dbcc forget_xact (xid)
dbcc dbrepair (database_name, dropdb)
dbcc engine( {offline , [enginenum] | "online" })
dbcc fix_text ({table_name | table_id})
dbcc indexalloc ({table_name | table_id}, index_id
[, {full | optimized | fast | null}
[, fix | nofix]])
dbcc rebuild_text (table [, column [, text_page_number]])
dbcc reindex ({table_name | table_id})
dbcc tablealloc ({table_name | table_id}
[, {full | optimized | fast | null}
[, fix | nofix]])|
dbcc { traceon | traceoff } (flag [, flag ... ])
dbcc tune ( { ascinserts, {0 | 1 } , tablename |
cleanup, {0 | 1 } |
cpuaffinity, start_cpu {, on| off } |
des_greedyalloc, dbid, object_name,
" { on | off }" |
deviochar vdevno, "batch_size" |
doneinproc { 0 | 1 } |
maxwritedes, writes_per_batch })
checks the specified database to see that all pages are correctly allocated and that no page that is allocated is not used. If no database name is given, checkalloc checks the current database. It always uses the optimized report option (see tablealloc).
checkalloc reports on the amount of space allocated and used.
is the name of the database to check. If no database name is given, dbcc uses the current database.
determines whether dbcc fixes the allocation errors found. The default mode for checkalloc is nofix. You must put the database into single-user mode to use the fix option.
For a discussion of page allocation in Adaptive Server, see the System Administration Guide.
checks for consistency in and between system tables. For example, checkcatalog makes sure that every type in syscolumns has a matching entry in systypes, that every table and view in sysobjects has at least one column in syscolumns, and that the last checkpoint in syslogs is valid. checkcatalog also reports on any segments that have been defined. If no database name is given, checkcatalog checks the current database.
runs the same checks as checktable, but on each table, including syslogs, in the specified database. If no database name is given, checkdb checks the current database.
causes dbcc checktable or dbcc checkdb to skip checking the nonclustered indexes on user tables. The default is to check all indexes.
checks the specified database for allocation, OAM page entries, page consistency, text valued columns, allocation of text valued columns, and text column chains. The results of each dbcc checkstorage operation are stored in the dbccdb database. For details on using dbcc checkstorage, and on creating, maintaining, and generating reports from dbccdb, see the System Administration Guide.
checks the specified table to see that index and data pages are correctly linked, that indexes are in properly sorted order, that all pointers are consistent, that the data information on each page is reasonable, and that page offsets are reasonable. If the log segment is on its own device, running dbcc checktable on the syslogs table reports the log(s) used and free space. For example:
Checking syslogs The total number of data pages in this table is 1. *** NOTICE: Space used on the log segment is 0.20 Mbytes, 0.13%. *** NOTICE: Space free on the log segment is 153.4 Mbytes, 99.87%.DBCC execution completed. If dbcc printed error messages, see your System Administrator.
If the log segment is not on its own device, the following message appears:
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
is the name or object ID of the table to check.
verifies the results of the most recent run of dbcc checkstorage for the specified database. For details on using dbcc checkverify, see the System Administration Guide.
heuristically completes a transaction by either committing or rolling back its work. Adaptive Server retains information about all heuristically completed transactions in the master.dbo.systransactions table, so that the external transaction coordinator may have some knowledge of how the transaction was completed.
WARNING! Heuristically completing a transaction in the prepared state can cause inconsistent results for an entire distributed transaction. The System Administrator’s decision to heuristically commit or roll back a transaction may contradict the decision made by the coordinating Adaptive Server or protocol.
removes the commit status of a heuristically completed transaction from master.dbo.systransactions. forget_xact can be used when the System Administrator does not want the coordinating service to have knowledge that a transaction was heuristically completed, or when an external coordinator will not be available to clear commit status in systransactions.
WARNING! Never use dbcc forget_xact in a normal DTP environment, since the external transaction coordinator should be permitted to detect heuristically-completed transactions. X/Open XA-compliant transaction managers and Adaptive Server transaction coordination services automatically clear the commit status in systransactions.
is a transaction name from the systransactions.xactname column. You can also determine valid xid values using sp_transactions.
drops a damaged database. drop database does not work on a damaged database.
Users cannot be using the database being dropped when this dbcc statement is issued (including the user issuing the statement).
takes Adaptive Server engines offline or brings them online. If enginenum is not specified, dbcc engine (offline) takes the highest-numbered engine offline. For more information, see Chapter 8, “Managing Multiprocessor Servers,” in the System Administration Guide.
upgrades text values after an Adaptive Server’s character set has been changed from any character set to a new multibyte character set.
Changing to a multibyte character set makes the internal management of text data more complicated. Since a text value can be large enough to cover several pages, Adaptive Server must be able to handle characters that span page boundaries. To do so, the server requires additional information on each of the text pages. The System Administrator or table owner must run dbcc fix_text on each table that has text data to calculate the new values needed. For more information, see the System Administration Guide.
checks the specified index to see that all pages are correctly allocated and that no page that is allocated is not used. This is a smaller version of checkalloc, providing the same integrity checks on an individual index.
indexalloc produces the same three types of reports as tablealloc: full, optimized, and fast. If no type is indicated, or if you use null, Adaptive Server uses optimized. The fix | nofix option functions the same with indexalloc as with tablealloc.
You
can specify fix or nofix only
if you include a value for the type of report (full, optimized, fast,
or null).
is the table name or the table’s object ID (the id column from sysobjects) plus the index’s indid from sysindexes.
reports all types of allocation errors.
produces a report based on the allocation pages listed in the object allocation map (OAM) pages for the index. It does not report and cannot fix unreferenced extents on allocation pages that are not listed in the OAM pages. The optimized option is the default.
does not produce an allocation report, but produces an exception report of pages that are referenced but not allocated in the extent (2521-level errors).
determines whether indexalloc fixes the allocation errors found in the table. The default is fix for all indexes except indexes on system tables, for which the default is nofix. To use the fix option with system tables, you must first put the database in single-user mode.
You can specify fix or nofix only if you include a value for the type of report (full, optimized, fast, or null).
rebuilds or creates an internal Adaptive Server 12.x data structure for text or image data. This data structure enables Adaptive Server to perform random access and asynchronous prefetch during data queries.
checks the integrity of indexes on user tables by running a fast version of dbcc checktable. It can be used with the table name or the table’s object ID (the id column from sysobjects). reindex prints a message when it discovers the first index-related error, then drops and re-creates the suspect indexes. The System Administrator or table owner must run dbcc reindex after Adaptive Server’s sort order has been changed and indexes have been marked “suspect” by Adaptive Server.
When dbcc finds corrupt indexes, it drops and re-creates the appropriate indexes. If the indexes for a table are already correct, or if the table has no indexes, dbcc reindex does not rebuild the index, but prints an informational message instead.
dbcc reindex aborts if a table is suspected of containing corrupt data. When that happens, an error message instructs the user to run dbcc checktable. dbcc reindex does not allow reindexing of system tables. System indexes are checked and rebuilt, if necessary, as an automatic part of recovery after Adaptive Server is restarted following a sort order change.
checks the specified table to see that all pages are correctly allocated and that no page that is allocated is not used. This is a smaller version of checkalloc, providing the same integrity checks on an individual table. It can be used with the table name or the table’s object ID (the id column from sysobjects). For an example of tablealloc output, see the System Administration Guide.
Three types of reports can be generated with tablealloc: full, optimized, and fast. If no type is indicated, or if you use null, Adaptive Server uses optimized.
is equivalent to checkalloc at a table level; it reports all types of allocation errors.
produces a report based on the allocation pages listed in the object allocation map (OAM) pages for the table. It does not report and cannot fix unreferenced extents on allocation pages that are not listed in the OAM pages. The optimized option is the default.
does not produce an allocation report, but produces an exception report of pages that are referenced but not allocated in the extent (2521-level errors).
determines whether or not tablealloc fixes the allocation errors found in the table. The default is fix for all tables except system tables, for which the default is nofix. To use the fix option with system tables, you must first put the database in single user mode.
You can specify fix or nofix only if you include a value for the type of report (full, optimized, fast, or null).
toggles the printing of diagnostics during query optimization (flag values 302, 310, and 317). Values 3604 and 3605 toggle sending trace output to the user session and to the error log, respectively. For more information, see Chapter 37, “Tuning with dbcc traceon” in the Performance and Tuning Guide.
enables or disables tuning flags for special performance situations. For more information on the individual options, see the Performance and Tuning Guide.
Checks pubs2 for page allocation errors:
dbcc checkalloc(pubs2)
Checks database consistency for pubs2 and places the information in the dbccdb database:
dbcc checkstorage(pubs2)
Adaptive Server returns an optimized report of allocation for this table, but does not fix any allocation errors:
dbcc tablealloc(publishers, null, nofix)
Checking salesdetail The total number of pages in partition 1 is 3. The total number of pages in partition 2 is 1. The total number of pages in partition 3 is 1. The total number of pages in partition 4 is 1. The total number of data pages in this table is 10. Table has 116 data rows. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
dbcc checktable(salesdetail)
Adaptive Server returns a full report of allocation for the index with an indid of 2 on the titleauthor table and fixes any allocation errors:
dbcc indexalloc ("pubs..titleauthor", 2, full)
Rebuilds or creates an internal Adaptive Server 12.x data structure for all text and image columns in the blurbs table:
dbcc rebuild_text (blurbs)
dbcc reindex has discovered one or more corrupt indexes in the titles table:
dbcc reindex(titles)
One or more indexes are corrupt. They will be rebuilt.
Upgrades text values for blurbs after a character set change:
dbcc fix_text(blurbs)
Heuristically aborts the transaction, “distributedxact1”:
dbcc complete_xact (distributedxact1, "rollback")
Removes information for the transaction, “distributedxact1” from master.dbo.systransactions:
dbcc forget_xact (distributedxact1)
dbcc, the Database Consistency Checker, can be run while the database is active, except for the dbrepair(database_name, dropdb) option and dbcc checkalloc with the fix option.
dbcc locks database objects as it checks them. For information on minimizing performance problems while using dbcc, see the dbcc discussion in the System Administration Guide.
To qualify a table or an index name with a user name or database name, enclose the qualified name in single or double quotation marks. For example:
dbcc tablealloc("pubs2.pogo.testtable")
dbcc reindex cannot be run within a user-defined transaction.
dbcc fix_text can generate a large number of log records, which may fill up the transaction log. dbcc fix_text is designed so that updates are done in a series of small transactions: in case of a log space failure, only a small amount of work is lost. If you run out of log space, clear your log and restart dbcc fix_text using the same table that was being upgraded when the original dbcc fix_text failed.
If you attempt to use select, readtext, or writetext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message instructs you to run dbcc fix_text on the table. However, you can delete text rows after changing character sets without running dbcc fix_text.
dbcc output is sent as messages or errors, rather than as result rows. Client programs and scripts should check the appropriate error handlers.
If a table is partitioned, dbcc checktable returns information about each partition.
text and image data that has been upgraded to Adaptive Server version 12.x is not automatically upgraded to the new storage format. To improve query performance and enable prefetch for this data, use the rebuild_text keyword against the upgraded text and image columns.
SQL92 – Compliance level: Transact-SQL extension.
Only the table owner can execute dbcc with the checktable, fix_text, rebuild_text, or reindex keywords.
Only the Database Owner can use the checkstorage, checkdb, checkcatalog, checkalloc, indexalloc, and tablealloc keywords.
Only a System Administrator can use the dbrepair, complete_xact, and forget_xact keywords.
Only a System Administrator can use dbcc traceon and dbcc traceoff commands.
Only a System Administrator can use dbcc engine.
Commands drop database
System procedures sp_configure, sp_helpdb
