Microsoft KB Archive/308795

= FIX: CheckDB May Not Fix Error 8909 or Error 8905 =

Article ID: 308795

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.

Additional query words: DBCC NEWALLOC CHECKALLOC CHECKTABLE objid, indid

Keywords: kbbug kbfix KB308795

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.