dbcc

Description

Database Consistency Checker (dbcc) checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality.

Syntax

dbcc addtempdb( dbid | databbase_name )
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 pravailabletempdbs
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 }})

Parameters

addtempdb

adds a temporary database to the global list of available temporary databases. If the database does not exist or is not a temporary database, an error is generated. If the database is already a member of the list, an informational message prints.

dbid

is the database ID.

database_name

is the name of the database to check. If no database name is given, dbcc uses the current database.

checkalloc

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.

fix | nofix

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.

checkcatalog

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.

checkdb

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.

skip_ncindex

causes dbcc checktable or dbcc checkdb to skip checking the nonclustered indexes on user tables. The default is to check all indexes.

checkstorage

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.

checktable

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.
table_name | table_id

is the name or object ID of the table to check.

checkverify

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.

complete_xact

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.

forget_xact

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.

xid

is a transaction name from the systransactions.xactname column. You can also determine valid xid values using sp_transactions.

dbrepair (database_name, dropdb)

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).

fengine

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.

fix_text

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.

indexalloc

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.

NoteYou can specify fix or nofix only if you include a value for the type of report (full, optimized, fast, or null).

table_name | table_id, index_id

is the table name or the table’s object ID (the id column from sysobjects) plus the index’s indid from sysindexes.

full

reports all types of allocation errors.

optimized

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.

fast

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).

pravailabletempdbs

prints the global list of available temporary databases.

fix | nofix

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).

rebuild_text

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.

reindex

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.

tablealloc

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.

full

is equivalent to checkalloc at a table level; it reports all types of allocation errors.

optimized

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.

fast

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).

fix | nofix

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).

traceon | traceoff

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.

tune

enables or disables tuning flags for special performance situations. For more information on the individual options, see the Performance and Tuning Guide.

Examples

Example 1

Checks pubs2 for page allocation errors:

dbcc checkalloc(pubs2)

Example 2

Checks database consistency for pubs2 and places the information in the dbccdb database:

dbcc checkstorage(pubs2)

Example 3

checks the salesdetail table:.

dbcc checktable(salesdetail)
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.

Example 4

Heuristically aborts the transaction, “distributedxact1”:

dbcc complete_xact (distributedxact1, "rollback")

Example 5

Upgrades text values for blurbs after a character set change:

dbcc fix_text(blurbs)

Example 6

Removes information for the transaction, “distributedxact1” from master.dbo.systransactions:

dbcc forget_xact (distributedxact1)

Example 7

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)

Example 8

Prints the global list of available temporary databases:

dbcc pravailabletempdbs
Available temporary databases are:
Dbid: 2
Dbid: 4
Dbid: 5
Dbid: 6
Dbid: 7
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Example 9

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)

Example 10

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.

Example 11

Adaptive Server returns an optimized report of allocation for this table, but does not fix any allocation errors:

dbcc tablealloc(publishers, null, nofix)

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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.

See also

Commands drop database

System procedures sp_configure, sp_helpdb