2

We recently experienced a database corruption and run integrity checks. DBCC CHECKDB returned a lot of consistency errors and 2 allocation errors. We have fixed consistency errors by moving data, droping and recreating related objects.

Now we have left 2 allocation errors and we don't know how to resolve them. Can anyone advice us on how to find the cause of these errors?

Below is the code we run;

DBCC CHECKALLOC([DBNAME]) WITH NO_INFOMSGS

The results;

Msg 8905, Level 16, State 1, Line 1 Extent (1:9612464) in database ID 22 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1 Extent (1:9617504) in database ID 22 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

CHECKALLOC found 2 allocation errors and 0 consistency errors not associated with any single object.

CHECKALLOC found 2 allocation errors and 0 consistency errors in database 'DBNAME'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKALLOC (DBNAME, noindex).

2

I would suggest to take a backup and restore the database to a different server and then run DBCC CHECKDB ...repair_allow_data_loss. This way you will know if there is any data loss or not.

Refer to my answer for more details.

If you have a good backup that you can restore, I would prefer to go with that and export the data out of the corrup database. You can use redgate's data compare (not free, but I have used it and its a lifesaver).

Make sure you do diagnosis of your hardware and update driver firmware to latest version and are on the latest supported service pack for sql server 2008R2.

  • Thanks @Kin. I have restored a copy of db and run REPAIR_ALLOW_DATA_LOSS. It fixed both allocation errors. But I am not sure if any data is lost. Is there a way to find what kind of data is lost? Result message is; ` 'Repair: The extent (1:9617504) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594965786624 (type Unknown).' Extent (1:9612464) in database ID 32 is marked allocated in the GAM, but no SGAM or IAM has allocated it. The error has been repaired. ` – Decoded Mar 3 '16 at 16:32
  • just do a row count from the original and restored copy. – Kin Shah Mar 3 '16 at 16:50
  • I did it and there is no data loss. All corruptions are fixed. Thank you again. – Decoded Mar 8 '16 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.