DBCC

At the end of a restore sequence that continues despite errors, you may be able to repair the database with DBCC CHECKDB.

To run DBCC checks, back up a database and its transaction log, update statistics, and rebuild indexes.

The definition of a unit of progress depends on the current phase of execution of the DBCC command.

To determine the transaction isolation level currently set, use the DBCC USEROPTIONS statement as shown in the following example.

DBCC CHECKFILEGROUP uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks.

Run the appropriate Database Console Commands (DBCC) on databases to be upgraded to ensure they are in a consistent state.

Some job steps, such as long-running Transact-SQL statements (BACKUP) or some DBCC commands, may not respond quickly to stop requests.

DBCC CHECKTABLE uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks.

Some DBCC repairs, although logged, cannot be replicated by the Log Reader Agent.

When detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue.

Validation errors reported by this option cannot be fixed by using DBCC repair options.

DBCC CHECKDB has been enhanced to detect when any one of these counts becomes negative.

This DBCC statement corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index.

Displays the estimated amount of tempdb space required to run DBCC CHECKFILEGROUP with all the other specified options.

DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS updates the suspect_pages table to indicate each page that it has deallocated or repaired.

Used to synchronize differential backup operations with DBCC.

Must be specified to run DBCC maintenance commands with sqlmaint on computed columns.

Displays the estimated amount of tempdb space needed to run DBCC CHECKTABLE with all the other specified options.

Must be specified to run DBCC maintenance commands with.

If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking.

Space required to run DBCC CHECKFILEGROUP with all the other specified options.

Use DBCC CHECKIDENT to check the identity values of your tables and correct the values if you want.

DBCC CHECKCATALOG uses an internal database snapshot to provide the transactional consistency that it needs to perform these checks.

Use DBCC OPENTRAN to determine whether an open transaction exists within the transaction log.

The following DBCC commands can be used to enable, disable, and display the status of trace flags.

Space needed to run DBCC CHECKTABLE with all the other specified options.

You can use the DBCC OPENTRAN Transact-SQL reference to verify if there is an active transaction in a database at a particular time.

If no backup exists, running DBCC CHECKDB with a repair option specified corrects the errors reported.

Is modifiable only in single-user mode, the DBCC CHECKDB command cannot be run on it directly.

DBCC TRACESTATUS returns a column for the trace flag number and a column for the status.

Also, DBCC DBREINDEX offers the advantage of more optimizations than individual DROP INDEX and CREATE INDEX statements.

If a snapshot cannot be created DBCC CHECKCATALOG acquires an exclusive database lock to obtain the required consistency.

For more information about displaying statistics, see DBCC SHOW_STATISTICS (Transact-SQL).

Specifies options for the type of information returned by the DBCC statement.

Database Console Commands (DBCC) are now integrated with the Watson application.

Note that repair options cannot be specified to DBCC CHECKFILEGROUP.

DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks.

Accepts an optional parameter to run DBCC UPDATEUSAGE before returning space information for the table or index.

If the specified table or index is partitioned, DBCC SHOWCONTIG only displays the first partition of the specified table or index.

The Execute SQL task, DBCC CHECK CONSTRAINTS, runs a DBCC command on each iteration of the loop.

The DBCC command does not fail if the data collection process fails.

When this occurs, the DBCC command executes against the actual database.

DBCC CHECKCATALOG performs various consistency checks between system metadata tables.

If DBCC CHECKALLOC reports any errors, we recommend that you restore the database from the database backup instead of running a repair.

An unknown failure occurred that unexpectedly terminated the DBCC command.

If you can, run DBCC CHECKDB against the database: Search Books Online and KB for the any error numbers returned.

DBCC CHECKCONSTRAINTS constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table.

TABLOCK limits the checks that are performed; DBCC CHECKCATALOG is not run on the database, and Service Broker data is not validated.

However, if you run the DBCC CHECKDB statement to check for database corruption, the DBCC CHECKDB statement does not report any errors.

If one occurs, run DBCC CHECKDB to determine whether other objects in the database are also damaged.

The dump file contains the results of the DBCC CHECKTABLE command and additional diagnostic output.

Unlike DBCC INDEXDEFRAG, or ALTER INDEX with the REORGANIZE option, DBCC DBREINDEX is an offline operation.

If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKTABLE acquires a shared table lock to obtain the required consistency.

Use the DBCC TRACESTATUS command to determine which trace flags are currently active.

This indicates a metadata corruption that caused the DBCC command to terminate.

We also recommend that you periodically run DBCC CHECKDB with no options.

The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified.

DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table.

If any errors are reported by DBCC CHECKFILEGROUP, we recommend restoring the database from the database backup.

The suspect_pages table is also updated as necessary during a restore operation, a DBCC repair operation, or a drop database operation.

Selecting this option is the same as specifying a target_percent value when executing DBCC SHRINKDATABASE.

If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency.

Use DBCC CHECKIDENT to check the current identity value and compare it with the maximum value in the identity column.

If one occurs, run DBCC CHECKDB to determine the extent of the damage.

We recommend that you run DBCC UPDATEUSAGE on the upgraded database.

If a snapshot cannot be created, or the TABLOCK option is specified, DBCC CHECKFILEGROUP acquires locks to obtain the required consistency.

An unknown error occurred that terminated the DBCC command.

DBCC OUTPUTBUFFER displays the results sent to the specified client (session_id).

Running DBCC CHECKDB when there is already a lot of activity on the system therefore impairs DBCC performance for two reasons.

Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline.

You can use the DBCC OPENTRAN Transact-SQL reference to identify the oldest non-replicated transaction.

After recreating the index on the view, run DBCC CHECKTABLE on the view to verify that no differences remain.

Is specified, DBCC CHECKTABLE runs integrity checks only on that index, together with the heap or clustered index.

DBCC CHECKDB does not examine disabled indexes.

DBCC SHOWCONTIG determines whether the table is heavily fragmented.

DBCC emergency-mode repair failed because the database could not be started after rebuilding the transaction log.

This statement replaces the DBCC INDEXDEFRAG statement.

By default, DBCC CHECKTABLE performs parallel checking of objects.

Database snapshots are used internally by certain DBCC commands.

Parallel DBCC should typically be left enabled.

DBCC detected an unfinished online index build.

Trace flags, after they are enabled, remain enabled in the server until disabled by executing a DBCC TRACEOFF statement.

The DBCC command then runs the checks against this snapshot.

For information about setting trace flags, see DBCC TRACEON (Transact-SQL).

After the DBCC command is completed, this snapshot is dropped.

This statement replaces the DBCC DBREINDEX statement.

This phase is not executed when DBCC CHECKTABLE is executed.

Complete a full database consistency check (DBCC CHECKDB).

Causes DBCC CHECKTABLE to obtain a shared table lock instead of using an internal database snapshot.

Number of times the DBCC statement moved from one extent to another while the statement traversed the pages of the table or index.

An Execute SQL Task then runs, which executes a DBCC CHECKCONSTRAINTS command using the two variables.

If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation.

When TABLERESULTS is specified, DBCC SHOWCONTIG returns the following columns and also the nine columns described in the previous table.

The DBCC CHECKFILEGROUP command validates objects only in the specified filegroup.

Use DBCC UPDATEUSAGE to synchronize space-usage counters.

These data structure checks can be performed by using DBCC CHECKDB and DBCC CHECKTABLE.

Using PHYSICAL ONLY can greatly shorten run-time for DBCC CHECKDB on large databases.

Not having a consistent serialization of byte-ordered user-defined types causes error 2537 when DBCC CHECKDB is run.

Running DBCC CHECKALLOC or DBCC CHECKTABLE separately from DBCC CHECKFILEGROUP is not required.

After this, DBCC CHECKDB checks column-value integrity by default.

Next, a Transact-SQL task executes a DBCC CHECKCONSTRAINTS command using the two variables.

The NO_INFOMSGS option of DBCC is helpful, it makes DBCC return error messages only.

The output of several DBCC statements.

The DBCC DBREINDEX statement is deprecated so you should avoid using it in new applications.

Specifies that DBCC CHECKALLOC repair the found errors.

Specifies that DBCC CHECKTABLE repair the found errors.

Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot.

This option allows postponing the checks until a later time by using DBCC CHECKTABLE.

If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use.

After this, DBCC CHECKDB and DBCC CHECKTABLE check column-value integrity by default.

Space and causes a nonlinear increase in the time that is required for DBCC to complete its checks.

DBCC CHECKDB and related statements typically must read each allocated page from disk into memory so that it can be checked.

Specifies that a DBCC CHECKALLOC statement with the NOINDEX option be run in the database specified in.

This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.

This is equivalent to running DBCC CHECKTABLE on a small list of necessary system base tables.

DBCC CHECKFILEGROUP and DBCC CHECKDB are similar DBCC commands.

Specifies that a DBCC CHECKCATALOG (Transact-SQL) statement be run in the database specified in -D.

Clearing this option is the same as executing DBCC SHRINKDATABASE using the TRUNCATEONLY option.

Many DBCC commands can produce output in tabular form by using the WITH TABLERESULTS option.

DBCC could not obtain a lock on object O_ID because the lock request timeout period was exceeded.

When fillfactor is 0, DBCC DBREINDEX uses the fill factor value last specified for the index.

If DBCC CHECKTABLE is run against tempdb, it must acquire a shared table lock.

If a DBCC check detects that a database has become corrupted, the Watson application uploads that error report to Microsoft.

We still recommend that a full run of DBCC CHECKTABLE be performed periodically.

Run DBCC CHECKTABLE on the underlying table to verify there is a problem.

And FAST are specified, DBCC SHOWCONTIG returns a result set with only the following columns.

To learn the minimum repair level needed, run DBCC CHECKDB without a repair option.

Specifies that a DBCC CHECKDB statement or a DBCC CHECKDB statement with the NOINDEX option be run in the database specified in -D.

DBCC CHECKDB fails when it is run against.

In SQL Server 2000, a simple DBCC TRACEON (1204) is enough to enable deadlock reporting to the error log.

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server 2005.

For example, you might specify error number 2571 to respond to unauthorized attempts to invoke Database Console Commands (DBCC).

DBCC SSB CHECK SQL Server Service Broker objects are checked during this phase.

After the DBCC CATALOG command finishes, a message is written to the SQL Server error log.

In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values.

Best Practices In SQL Server 2005, running DBCC CHECKDB with no options may take considerably longer than in earlier versions.

After the DBCC CHECKTABLE command finishes, a message is written to the SQL Server error log.

After the DBCC CHECKDB command finishes, a message is written to the SQL Server error log.

For more information on workloads, batches, and DBCC, see the SQL Server documentation.

We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Additionally, see the "DBCC OPENTRAN" topic in SQL Server Books Online.

In SQL Server 2005, a full run of DBCC CHECKTABLE may take considerably longer than in earlier versions.

In SQL Server 2005, DBCC CHECKDB has been enhanced to detect when page or row counts become negative.

DBCC USEROPTIONS returns a column for the name of the SET option and a column for the value of the option (values and entries may vary)

When negative counts are detected, the DBCC CHECKDB output contains a warning and a recommendation to run

If the results of the DBCC statement indicate a problem exists, rebuild the index by using one of the following methods

To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem.

Whether any one of the options, except NO_INFOMSGS, is specified (the table name must be specified), DBCC DBREINDEX returns

An internal database snapshot is not created when a DBCC command is executed

To restrict the maximum number of processors available for DBCC checking, use

The DBCC commands use table locks instead of the internal database snapshots when the command is executed against the following

The following DBCC commands operate on an internal read-only database snapshot that SQL Server Database Engine creates.

The disk space usage for DBCC CHECKDB is increased due to the following changes in the DBCC CHECK operation

Troubleshooting DBCC Errors on Indexed Views

DBCC Database Base Consistency Checker

When a valid DLL is specified, DBCC dllname (FREE) returns

DBCC CHECKFILEGROUP returns the following result set (values may vary)

For the specified table, DBCC CHECKTABLE checks for the following

DBCC OUTPUTBUFFER returns the following (values may vary)

DBCC CHECKTABLE returns the following result set if the ESTIMATEONLY option is specified

Run DBCC CHECKDB to check for any corruption