ID Number: Q71508
1.10
OS/2
=====================================================================
QS0435: CORRECTING dpages INCONSISTENCIES REPORTED BY DBCC
=====================================================================
Revision Date: 6/91
-------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an Applica- |
| tion Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, |
| EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE |
| IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTI- |
| CULAR PURPOSE. The user assumes the entire risk as to the accuracy |
| and the use of this Application Note. This Application Note may be |
| copied and distributed subject to the following conditions: 1) All |
| text must be copied without modification and all pages must be |
| included; 2) If software is included, all files on the disk(s) |
| must be copied without modification (the DOS utility DISKCOPY is |
| appropriate for this purpose); 3) All components of this |
| Application Note must be distributed together; and 4) This |
| Application Note may not be distributed for profit. |
| |
| Copyright 1991 Microsoft Corporation. All rights reserved. |
| Microsoft, MS-DOS, and the Microsoft logo are registered |
| trademarks of Microsoft Corporation. |
--------------------------------------------------------------------
Reasons for Inconsistencies
---------------------------
The Database Consistency Checker (DBCC) options Checktable and Checkdb
may report warnings similar to the following warning:
Checking <table name or object number>
The total number of data pages in this table is xx.
Number of data pages counted by DBCC differs from count in
Sysindexes row. Please UPDATE 'dpages' in the Sysindexes row for
this object to equal the count just printed.
(Msg 2583, Level 0, State 1)
It is not unusual for the DBCC to find a discrepancy between the
actual page count and the count reflected in Sysindexes dpages for the
syslogs table. Because of the overhead involved, the page count in
Sysindexes (that is, the dpages) is not updated every time something
is logged. Instead, the changes are saved until a checkpoint command
is executed. Generally, this warning will occur on the syslogs table
(that is, object 8), but it can also occur on other user tables.
Although this discrepancy should be corrected, it is not a serious
problem because the value in Sysindexes is used only for reporting
space allocation, not for enforcing it.
Solution
--------
To correct the dpage inconsistency, first execute a Checkpoint command
in the database where the error occurred, and rerun the DBCC Checkdb
option (or Checktable) before any updates are performed on the
database. If this does not eliminate the warning, correct the
inconsistencies manually by following the steps listed below:
1. Determine the "id" and "indid" of the table with the
inconsistency. It is possible for the Sysindexes table to have more
than one entry for the table with the inconsistency. Use the
following query to obtain the necessary values for a table named
"my_table":
select id, indid from sysindexes
where id=object_id('my_table')
and indid < 2
indid will contain either of the following:
0 for a table with no clustered index
1 for a table with a clustered index
2. Use the "sp_configure" stored procedure to enable updates to the
system catalogs (allow updates = true).
3. Execute the SQL statement Reconfigure With Override to allow the
change in configuration to take effect.
4. Execute the following SQL statement in the database in which the
DBCC check was done:
update sysindexes
set dpages = <count from DBCC>
where id=<id of table> < --- ID from previous query
and indid = <0 or 1> < --- INDID from previous query
5. Execute the Checkpoint command in the database in which the DBCC
command was performed.
6. Use "sp_configure" to disable updates to the system catalogs
(allow updates = false).
Immediately run the DBCC. No warning should appear.