dbcc checktable

dbcc checktable checks the specified table to see that:

• Index and data pages are linked correctly

• Indexes are sorted properly

• Pointers are consistent

• All indexes and data partitions are correctly linked

• Data rows on each page have entries in the row-offset table; these entries match the locations for the data rows on the page

• Partition statistics for partitioned tables are correct

The syntax for dbcc checktable is:

dbcc checktable({table_name | table_id}[, skip_ncindex | “fix_spacebints“ [, “partition_name“ | partition_id]])

The skip_ncindex option allows you to skip checking the page linkage, pointers, and sort order on nonclustered indexes. The linkage and pointers of clustered indexes and data pages are essential to the integrity of your tables. You can drop and re-create nonclustered indexes if Adaptive Server reports problems with page linkage or pointers.

partition_name is the name of the partition you are checking (this may or may not contain the entire table because tables can span multiple partitions), and partition_id is the ID of the partition you are checking.

If you specify partition_name or partition_id, dbcc checktable checks only the table, or parts of the table, residing on this partition; it does not expand its check to other partitions, and has the following restrictions:

• If the table consists of more than one partition, index processing is limited to local indexes.

• If you specify the partition_name or partition_id parameter, you must also specify either the second parameter (skip_ncindex or fix_spacebits) or null. This example specifies null:

  dbcc checkalloc(titles, null, 560001995)
  

• If the sort order or character set for a table with columns defined with char or varchar datatypes is incorrect, dbcc checktable does not corrct these values. You must run dbcc checktable on the entire table to correct these errors.

• If an index is marked “read-only” due to a change in the sort order, dbcc checktable does not clear the O_READONLY bit in the sysstat field for the table’s sysobjects entry. To clear this status bit, you must run dbcc checktable on the entire table.

• If you run dbcc checktable on syslogs, dbcc checktable does not report space usage (free space versus used space). However, if you do not specify partition_name or partition_id parameters, dbcc checktable reports the space usage.

When checkstorage returns a fault code of 100035, and checkverify confirms that the spacebit fault is a hard fault, you can use dbcc checktable to fix the reported fault.

The following command checks part of the titles table that resides on the smallsales partition (which contains all book sales less than 5000):

dbcc checktable(titles, NULL, "smallsales")

Checking partition 'smallsales' (partition ID 1120003990) of table 'titles'.
The logical page size of this table is 8192 bytes. The total number of data
pages in partition 'smallsales' (partition ID 1120003990) is 1.
Partition 'smallsales' (partition ID 1120003990) has 14 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

The following is the dbcc checktable syntax, where table_name is the name of the table to repair:

dbcc checktable (table_name, fix_spacebits)

You can use dbcc checktable with the table name or the table’s object ID. The sysobjects table stores this information in the name and id columns.

The following example shows a report on an undamaged table:

dbcc checktable(titles)

Checking table 'titles' (object ID 576002052):Logical page size is 8192 bytes.
The total number of data pages in partition 'titleidind_576002052' (partition ID
576002052) is 1.
The total number of data pages in this table is 1.
Table has 18 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

To check a table that is not in the current database, supply the database name. To check a table owned by another object, supply the owner’s name. You must enclose any qualified table name in quotes. For example:

dbcc checktable("pubs2.newuser.testtable")

dbcc checktable addresses the following problems:

• If the page linkage is incorrect, dbcc checktable displays an error message.

• If the sort order (sysindexes.soid) or character set (sysindexes.csid) for a table with columns with char or varchar datatypes is incorrect, and the table’s sort order is compatible with Adaptive Server’s default sort order, dbcc checktable corrects the values for the table. Only the binary sort order is compatible across character sets.

  If you change sort orders, character-based user indexes are marked “read-only” and must be checked and rebuilt, if necessary.

• If data rows are not accounted for in the first OAM page for the object, dbcc checktable updates the number of rows on that page. This is not a serious problem. The built-in function row_count uses this value to provide fast row estimates in procedures like sp_spaceused.

You can improve dbcc checktable performance by using enhanced page fetching.

dbcc checkindex runs the same checks as dbcc checktable, but only on the specified index instead of the entire table. The synax is:

dbcc checkindex({table_name|table_id}, index_id, bottom_up [, partition_name | partition_id]])

partition_name is the name of the partition you are checking and partition_id is the ID of the partition you are checking. bottom_up specifies that checkindex checks from the bottom up, starting at the leaf of the index. bottom_up is only applicable for DOL tables. If you specify this option with checkindex or checktable, the index checking is done in a bottom up fashion