SQL Server – Resolving DBCC CHECKALLOC Error Msg 8905

corruptionsql serversql-server-2008-r2

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

Best Answer

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.

Related Question