PRB: dpages Inconsistencies Reported by DBCC

ID Number: Q57237

1.10 1.11 4.20

OS/2

Summary:

SYMPTOMS

The database consistency checker (DBCC) options CHECKTABLE and

CHECKDB sometimes report warnings, stating that the value in the

dpages column of the sysindexes table is not the same as DBCC's

count of data pages for some table.

RESOLUTION

To correct this inconsistency, execute a CHECKPOINT command in the

database. This may take care of the problem. This error frequently

occurs on the syslogs table (the transaction log), as this table is

not updated every time an update is performed on the database. The

pending updates are saved, and are executed at a CHECKPOINT command.

Immediately after executing the CHECKPOINT command, run DBCC

CHECKTABLE (tablename) on the table in question. If you have a

version 4.2 SQL server, DBCC CHECKTABLE corrects the sysindexes

differences without having to continue with the next steps.

If this does not get rid of the warning, you may have to correct

the inconsistency yourself. To correct the problem by modifying

sysindexes, do the following:

1. Use the sp_configure stored procedure to enable updates to

system catalogs (allow updates = true).

2. Execute the SQL statement RECONFIGURE WITH OVERRIDE for that

change in configuration to take effect.

3. When using the database where the DBCC check was done, execute

the following SQL statement:

update sysindexes set dpages = <count from DBCC>

where id=<id of table> and indid <0 or 1>

4. Execute the CHECKPOINT command in the database where the DBCC

command was done.

5. Use sp_configure to disable updates to the system catalogs

(allow updates = false).

Immediately run DBCC. The warning should not appear.