DBCC

From DBArtisan
Jump to: navigation, search

Go Up to Available object actions by DBMS

The DBCC (Database Consistency Check) function box lets you:

  • Specify single or multiple tables or indexes for validation.
  • Perform database-wide validations.
  • Perform object-level validations of databases.

Platform Availability

To Perform a Database Consistency Check

  1. Initiate a DBCC action against a database, table, or index, as follows:
    • If you are checking consistency of a Sybase ASE or SQL Server database or a SQL Server table, right-click the object and select DBCC from the context menu, as appropriate.
    • If you are checking consistency of a Sybase ASE table, right-click the table and select either DBCC > Check Allocation, DBCC > Check Table, DBCC > Check Text, or DBCC > Rebuild Index from the context menu, as appropriate.
    • If you are checking consistency of a Sybase ASE index, right-click the index and select either DBCC > Check Allocation, or DBCC > Check Index from the context menu, as appropriate.
    • If you are checking consistency of a SQL Server index, right-click the table and select either DBCC > Check Fragmentation, DBCC > Update Usage, DBCC > Check Index, or DBCC > Show Statistics from the context menu, as appropriate.
    For more information see Initiating an object operation.
    A dialog that that lets you specify DBCC options opens.
  2. See the following topics as a guide to understanding and setting options on the dialog:
  3. Click Execute. For information on the scheduling option, see Scheduling.

DBCC for Microsoft SQL Server

The DBCC (Database Consistency Check) dialog lets you specify single or multiple tables or indexes for validation in Microsoft SQL Server. Use this dialog box to perform table-level or index-level validations of databases which are too large to undergo database-level DBCC operations in a time-efficient manner.

The DBCC dialog includes the following elements:

  • A window displaying the target database objects
  • A drop-down list of DBCC Operations
  • Buttons for previewing the operation’s SQL code, scheduling the operation, and executing the operation

DBCC for Microsoft SQL Server Databases

The DBCC dialog for databases lets you perform database-wide validations. You should validate your databases as part of regular database maintenance to guard against corruption and failure. Microsoft SQL Server offers a set of DBCC commands to validate the integrity of your databases. Generally, you should perform these DBCC commands prior to dumping your databases to ensure that you are capturing clean backups of your databases.

The fundamental difference between the DBCC dialog for databases, tables and indexes is the content of the DBCC Operation drop-down list.

The table below describes the options and functionality on the DBCC dialog.

DBCC Operation Description

Check Allocation

Executes a DBCC CHECKALLOC command. Makes sure that all data and index panels are correctly allocated and used. It reports on the amount of space allocated and used in the database. When checking allocation, you have the option to skip non-clustered indexes by selecting the Skip non-clustered indexes check box.

Check Catalog

Executes a DBCC CHECKCATALOG command. Checks for consistency in and between system tables.

Check Database

Executes a DBCC CHECKDB command. Verifies that all tables and indexes are properly linked, that indexes are in proper sorted order, that all pointers are consistent, that the data on each panel is reasonable, and that panel offsets are reasonable. When checking a database, you have the option to skip non-clustered indexes by selecting the Skip non-clustered indexes check box.

Check FileGroup

Executes a DBCC CHECKFILEGROUP command. Verifies that all tables and indexes for the specified filegroup are properly linked, that indexes are in proper sorted order, that all pointers are consistent, that the data on each panel is reasonable, and that panel offsets are reasonable. When checking filegroups, you have the option to skip non-clustered indexes by selecting the Skip non-clustered indexes check box.

Show Oldest Transaction

Executes a DBCC OPENTRAN command. Displays information on the oldest active transaction and the oldest distributed and non distributed replicated transactions, if any, within the specified database.

Update Usage

Executes a DBCC UPDATEUSAGE command. Reports and corrects the rows, used, reserved, and dpanels columns of the sysindexes table for any clustered indexes on objects of the type U (user-defined table) or S (system table).

DBCC for Microsoft SQL Server Tables

The DBCC dialog for tables lets you perform table-level validations of databases. The fundamental difference between the DBCC dialog for tables and indexes is the content of the DBCC Operation drop-down list.

The table below describes the options and functionality on the DBCC dialog.

Option Description

Check Current Identity Value

Checks the current identity value for the target objects, correcting values if needed depending on parameter specifications. Identity columns created with a NOT FOR REPLICATION clause in either the CREATE TABLE or ALTER TABLE statement are not corrected by this operation.

Check Fragmentation

Displays the target table's data and index fragmentation information, determining whether the table is heavily fragmented. When a table is heavily fragmented, you can reduce fragmentation and improve read performance by dropping and recreating a clustered index (without using the SORTED_DATA option). Doing so reorganizes the data, resulting in full data pages. To adjust the level of fullness, use the Rebuild Index operation’s FILLFACTOR option. When INSERT, UPDATE, and DELETE statements fragment tables, they usually do so with unequal distribution across the entire database so that each page varies in fullness over time, forcing additional page reads for queries that scan part or all of a table.

Check Table

Checks the linkages and sizes of text, ntext and image pages for selected tables. For the data, index, text, ntext, and image pages of the target tables, this operation also checks that index and data pages are correctly linked, indexes are in their proper sorted order, pointers are consistent, the data on each page is reasonable, and the page offsets are reasonable. DBCC CHECKTABLE requires a shared lock on all tables and indexes in the database for the duration of the operation. However, DBCC CHECKTABLE does not check the allocations of pages in the specified table (for this, use DBCC CHECKALLOC). To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

Check Text/Image Allocation

Checks the allocation of text, ntext, or image columns for a table. In later versions of Microsoft SQL, use DBCC CHECKTABLE to check the integrity of the data, index, text, ntext, and image pages for the target table.

Pin Table

Pins target tables in memory so that they are not flushed when Microsoft SQL Server needs space to read in new pages. DBCC PINTABLE is best used for keeping small, frequently referenced tables in memory. Pinning a large table can consume a large portion of the buffer cache, leaving inadequate memory to service other tables in the system. A pinned table that is larger than the buffer cache itself can fill the entire cache, necessitating a shut down of the system by a sysadmin user, who must then restart Microsoft SQL Server and unpin the table. Pinning too many small tables can result in a similar problem.

Rebuild Index

Dynamically rebuilds one, multiple, or all indexes for a table in the target database, allowing indexes which enforce either primary key or unique constraints to be rebuilt without need for dropping and recreating. This operation is not supported for use on system tables.

Unpin Table

Marks target tables as unpinned, rendering their pages flushable from the buffer cache if space is needed to read in a new page from disk.

Update Usage

Reports and corrects inaccuracies in the sysindexes table (which can result in incorrect space usage reports by the sp_spaceused system stored procedure) and corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes. If there are no inaccuracies in sysindexes, DBCC UPDATEUSAGE returns no data. Use this operation to synchronize space-usage counters. Executing this operation on large tables or databases can require some time, so it should typically be used only when you suspect incorrect values returned by sp_spaceused.

DBCC for Microsoft SQL Server Indexes

The following Database Console Commands are supported, letting you perform index-level validations of databases.

Option Description

Check Fragmentation

Lets you build and submit a DBCC SHOWCONTIG command, to display fragmentation information for the selected index, letting you determine whether the table is heavily fragmented. When a table is heavily fragmented, you can reduce fragmentation and improve read performance by dropping and recreating a clustered index. For details, see Rebuild Index (SQL Server).

Update Usage

Lets you build and submit a DBCC UPDATEUSAGE command, reporting on and correcting pages and row count inaccuracies that can result in incorrect space usage reports. The Count Rows control lets you add a WITH COUNT_ROWS argument, dictating that the row count column be corrected as necessary. The No Informational Messages control lets you add a WITH NO_INFOMSGS argument, suppressing all informational messages.

Check Index

Lets you build and submit a DBCC CHECKTABLE command, verifying the integrity of pages and structures of the table or indexed view. Disabling the Show Informational Messages control adds a WITH NO_INFOMSGS argument, suppressing informational messages.

Show Statistics

Lets you build and submit a DBCC SHOW_STATISTICS command, displaying current query optimization statistics for the owning table or indexed view.

DBCC for Sybase ASE

The DBCC (Database Consistency Check) dialog lets you specify single or multiple databases, tables or indexes for validation in Sybase ASE. Use this dialog box to perform table-level or index-level validations of databases which are too large to undergo database-level DBCC operations in a time-efficient manner.

The DBCC dialog includes the following elements:

  • A window displaying the target database objects
  • A drop-down list of DBCC Operations
  • Buttons for previewing the operation’s SQL code, scheduling the operation, and executing the operation

DBCC for Sybase ASE Databases

The DBCC dialog for databases lets you perform database-wide validations. The fundamental difference between the DBCC dialog for databases, tables and indexes is the content of the DBCC Operation drop-down list.

The table below describes the options and functionality on the DBCC dialog.

Option Description

Check Allocation

Checks the allocation and use of all pages in the target database.

Check Catalog

Checks for consistency in and between system tables in the target database.

Check Database

Checks the allocation and structural integrity of all the objects in the target database.

Check Storage

Checks the target database for allocation, OAM page entries, page consistency, text valued columns, allocation of text valued columns, and text column chains. The results of this operation are stored in the dbccdb database.

Database Repair

Drops a damaged database.

DBCC for Sybase ASE Tables

The DBCC dialog for tables lets you perform table-level validations of databases. The fundamental difference between the DBCC dialog for tables and indexes is the content of the DBCC Operation drop-down list.

The table below describes the options and functionality on the DBCC dialog.

Option Description

Check Allocation

Checks the database to see that every page is correctly allocated, and that no allocated page is unused. Use TABLEALLOC frequently (daily) to check page linkages in the Adaptive Server before performing a database dump to ensure the integrity of the dumped data.

Check Table

Checks the linkages and sizes of text, ntext and image pages for selected tables. For the data, index, text, ntext, and image pages of the target tables, this operation also checks that index and data pages are correctly linked, indexes are in their proper sorted order, pointers are consistent, the data on each page is reasonable, and the page offsets are reasonable. DBCC CHECKTABLE requires a shared lock on all tables and indexes in the database for the duration of the operation. However, DBCC CHECKTABLE does not check the allocations of pages in the specified table (for this, use DBCC CHECKALLOC). To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

Check Text

Upgrades text values after you have changed an Adaptive Server's character set to a multibyte character set.

Rebuild Index

Dynamically rebuilds one, multiple, or all indexes for a table in the target database, allowing indexes which enforce either primary key or unique constraints to be rebuilt without need for dropping and recreating. This operation is not supported for use on system tables.

DBCC Operation Options

Additional options are offered for selected operations which you can specify to further customize a database consistency check. The table below describes each option:

Option Description

Error Option

Click Fix Error to have any allocation errors fixed. You must put your database in single-user mode to fix errors, so specify this option during times of low usage.

Job Scope

Select Optimize to produce 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. Select Full to perform the equivalent of a table-level CHECKALLOC, reporting all types of allocation errors. Select Fast to produce an exception report of pages that are referenced but not allocated in the extent. Fast does not produce an allocation report.

Update Index Option

Click this check box to skip non-clustered indexes when updating index options.

DBCC for Sybase ASE Indexes

The DBCC dialog for indexes lets you perform index-level validations of databases. Unlike the DBCC dialog for tables, this DBCC dialog offers only one option on the DBCC Operation drop-down list: Check Allocation. This option checks the specified database to see that all pages are correctly allocated and that no allocated page is unused.

The table below describes the options and functionality on the DBCC dialog.

Option Description

DBCC Option

Checks the specified database to see that all pages are correctly allocated and that no page that is allocated is not used.

Error Option

Any allocation errors detected are fixed. You must put your database in single-user mode to fix errors, so specify this option during times of low usage

Job Scope

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. A full job is the equivalent to a table-level CHECKALLOC, reporting all types of allocation errors. A fast job does not produce an allocation report, but produces an exception report of pages that are referenced but not allocated in the extent.