FIX: CheckDB May Not Fix Error 8909 or Error 8905 (308795)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q308795
BUG #: 353867 (SHILOH_BUGS)

SYMPTOMS

If DBCC CHECKDB reports error 8909 on pages allocated for sorting, when you run DBCC CHECKDB with the repair_allow_data_loss option, DBCC CHECKDB reports that the error is fixed even though the error is not fixed. Error 8905 usually accompanies groups of eight or more occurrences of error 8909. Regardless of the repair option set, a subsequent execution of DBCC CHECKDB reports the same set of 8909 and 8905 error messages.

A sample of these error messages are:
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:192) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

-or-

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:192). The PageId in the page header = (0:0).

CAUSE

A DBCC CHECKDB statement with a repair option mistakenly reports that error 8909 is fixed. This problem only occurs for extents and pages allocated for sorting. Pages used for sorting have the following field values:
  • Page type is set to SORT_PAGE (7).
  • Object ID is 0.
  • Index ID is 0.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

To fix this problem CHECKDB must force the allocation repair to remove extents that only contain pages allocated to objid and indid 0.

To resolve this problem, install SQL Server 2000 Service Pack 2, and then run DBCC CHECKDB with the repair_allow_data_loss option.
To resolve the problem without applying SQL Server 2000 Service Pack 2, you can use Data Transformation Services (DTS) to transfer the database out.

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbbug kbfix KB308795
©2003 Microsoft Corporation. All rights reserved.