"Repairing this error requires other errors to be corrected first"

Last Post 09 Jun 2010 09:00 PM by Davidpoul. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
murk
New Member
New Member

--
23 Mar 2003 10:59 PM
I've got a problem with allocation errors reported by DBCC

I'm running SQL Server 7.0 SP3.

I'm using DBCC as follows:

dbcc checkdb ('prod',repair_allow_data_loss) with all_errormsgs

I get a lot of lines reporting error 8905 and 8906 - e.g.:

Server: Msg 8906, Level 16, State 1, Line 0
Page (1:2611364) in database ID 8 is allocated in the SGAM (1:2556161) and PFS (1:2604336), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.

and

Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:6945928) in database ID 8 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:6945936) in database ID 8 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:6945944) in database ID 8 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

and then the following:
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.

This pattern repeats for several hundred lines (Obvously this is edited for length!) The net result is that 425 allocation errors are found but 0 are fixed:

CHECKDB found 425 allocation errors and 0 consistency errors not associated with any single object.

the pass through the tables does not identify any further errors and the batch completes with:

CHECKDB found 425 allocation errors and 0 consistency errors in database 'PROD'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PROD repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If I then run DBCC again, the same 425 allocation errors are reported - they never get fixed!!

Can anyone suggest a solution????

Thanks

murk
New Member
New Member

--
24 Mar 2003 04:50 AM
Yes - I got the same results (only sooner!)
murk
New Member
New Member

--
22 May 2003 02:32 AM
We've now migrated our database onto a new server and are still experiencing exactly the same problem. Can anyone suggest any solutions? - I have around 20Gb of unusable space in my MDF file as I can't get checkalloc to fix these errors.
satya
New Member
New Member

--
22 May 2003 03:15 AM
Apply the latest SP on SQLServer, and run DBCC CHECKDB with repair_allow_data_loss option. For safeguard of data use DTS to export the data to another table.
murk
New Member
New Member

--
22 May 2003 05:28 AM
Already tried that!!

The dbcc fails with the error messages described above. My db is 120Gb so I don't really want to have to DTS the data all out and recreate it since I'll probably need to take it down for a whole week to do thagt (plus I don't have 120Gb of free online storage).
satya
New Member
New Member

--
22 May 2003 11:13 PM
Wait a minute, do you have any concurrent execution of DBCC SHRINKDB & SHRINKFILE?
If so try to serialize them in order and wait until one job finishes.

DBCC CHECKDB or CHECKALLOC with REPAIR should correct these errors. DBCC UPDATEUSAGE should then be used to also correct sysindexes entries.

As specified apply the latest SP for SQL 7.
Davidpoul
New Member
New Member

--
09 Jun 2010 09:00 PM
To resolve the above issue and access the SQL database, you needs to execute DBCC CHECKDB command with repair_allow_data_loss option. This command performs complete repair and also includes allocation and de-allocation of pages for rectifying allocation errors and deletion of corrupted text. However, this repair utility can also result in loss of data. In such scenarios, you needs to recover the lost data by using effective sql database recovery application.
You are not authorized to post a reply.

Acceptable Use Policy