CHECKDB Fails to Fix Error 8909 and 8905

DBCC CHECKDB commands allow checking logical and physical SQL Server database integrity. When used with REPAIR_ALLOW_DATA_LOSS argument, it repairs all reported errors, but with the possibility of data loss. The corrupted data gets deleted in the process. But at times, when you run DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS argument, database corruption is reported as fixed though is not corrected. In such situations, you can use your recent data backup to restore the database. But if the backup fails to restore or is not present, you need to scan your damaged database with powerful SQL recovery applications.

Consider a practical instance, you scan your SQL Server 2000 database and get 8909 error, followed by a group of eight or more 8909 error messages:

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).

Further, you use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option and it reports it as fixed. But again, when you run DBCC CHECKDB command to check database integrity, it reports the same error messages.

Cause

You encounter this issue when the following conditions are true:

You run DBCC CHECKDB on pages and extents that are allocated for sorting
Pages have field values such as Page type is set to SORT_PAGE (7), Index ID is 0 and Object ID is 0
You haven’t installed SQL Server 2000 Service Pack 2 or later.

Solution

To resolve the issue, you can follow any of these methods:

Install SQL Server 2000 Service Pack 2 and run DBCC CHECKDB command with repair_allow_data_loss option
Use DTS (Data Transformation Services) and transfer the database out.

If the above measures don’t help or the database still appears to be corrupt, you need to use efficient third-party SQL repair applications. These are the commercial tools provided to scan and repair damaged SQL Server databases. You can use these SQL Recovery products in any case of SQL database corruption.

SQL Recovery is the finest SQL Repair tool to scan and repair damaged or corrupt SQL Server databases. The software performs safe repair operations and restores all database components. It is compatible with SQL Server 2005 and 2000. The software supports Windows Vista, XP, 2003, 2000 and NT.