PRB: DBCC Reports Page Count Discrepancy on SYSLOGS Table

  - Microsoft SQL Server, version 4.2 for OS/2
  - Microsoft SQL Server, versions 4.2 and 6.0

SYMPTOMS

DBCC reports a page count discrepancy in the SYSLOGS table.

CAUSE

It is not unusual for DBCC to find a discrepancy between the actual page count and the count reflected in SYSINDEXES DPAGES for the SYSLOGS table. This discrepancy occurs because the page count in SYSINDEXES (DPAGES) is not updated every time something is logged; that would cause too much overhead. Instead, the changes are saved until a CHECKPOINT is executed.

The discrepancy does not cause problems because the value in SYSINDEXES is used only for reporting space allocation, not for enforcing it. Also, the occasionally erroneous value in SYSINDEXES never affects the choice of access strategy because queries are never run on SYSLOGS.

WORKAROUND

Run a CHECKPOINT command on the database in question and recheck SYSLOGS with DBCC CHECKTABLE. (Do not allow any updates to be made to that database between the CHECKPOINT and the DBCC).

SQL Server reports DPAGE discrepancies when CHECKTABLE or CHECKDB is run; however, it also automatically corrects the DPAGE count in SYSINDEXES.

MORE INFORMATION

There is a situation that seems to produce a discrepancy between the actual page count and the SYSINDEXES page count that is NOT resolved by doing a CHECKPOINT. If a database that does not have its transaction log on a separate database device is ever allowed to have the log fill up (requiring it to be purged with DUMP TRANSACTION WITH NO_LOG), SYSINDEXES seems to get permanently out of sync with the actual page count.

It appears that if the actual page count is higher than the SYSINDEXES page count, CHECKPOINT resolves the discrepancy, but if the reverse is true, it does not.