DBCC CHECKDB with allocation error

Question

DBCC CHECKDB with allocation error

talk_tina4610

SSC-Addicted

Points: 459
More actions
June 4, 2013 at 10:45 am

#274603

Hi,
After server shutdown in the midnight due to power failure, our DB backup maintenance plan stopped running.
Executing DBCC CHECKDB ('xxx') WITH NO_INFOMSGS
gave the following output:
Msg 8905, Level 16, State 1, Line 1
Extent (1:81936) in database ID 19 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'xxx'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (xxx).
My DB recovery mode is 'SIMPLE' and as such no option of recovering from Transaction logs.
In attempt to resolve this problem, I created a TEST DB from a previous day backup, which gave the same DBCC check result, and I used the recommended repair_allow_data_loss option.
DBCC CHECKDB ('xxx', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
which displayed the following result:
Msg 8905, Level 16, State 1, Line 2
Extent (1:81936) in database ID 19 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
The error has been repaired.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB fixed 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'xxx'.
CHECKDB fixed 1 allocation errors and 0 consistency errors in database 'xxx'.
In order to ascertain that there is no constraints problem after executing the REPAIR_ALLOW_DATA_LOSS, I executed:
DBCC CHECKCONSTRAINTS with No_infomsgs
Command(s) completed successfully.
Executing DBCC CHECKDB ('xxx') WITH NO_INFOMSGS again, also gave
Command(s) completed successfully.
Please I want to know if there are still chances of data loss in DB after the above processes?
I hope someone can help me out before I repeat the process for the production DB.
Thanks for your urgent reply.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Kirk.wilson SSC Veteran Points: 268 More actions · Answer 1

It sounds like you Index Allocation Map chains are broken. You may not have lost any data but your index/keys are corrupt. I had the same issue so I created a backup and then moved the data to a new database and rebuilt the indexes. I then compared row counts between the two and no data loss. Then create a backup from the good database and restore to prod.

talk_tina4610 SSC-Addicted Points: 459 More actions · Answer 2

Hi Wilson,

Thanks for your reply.

I feel more relived since there will be no data loss, but I’m kind of confused on how to go about rebuilding of the indexes, and the row count comparison.

Please can you help me the query I can use to achieve these?

I will really appreciate that.

Thanks.

Regards

Tina

Kirk.wilson SSC Veteran Points: 268 More actions · Answer 3

You right click the database in question and script it out. There is an advanced tab and in there you can have it create the indexes and keys as well. You would want to run the script to create the database without data. I did not include the indexes when I did it but I do not think that will be a problem. You may have to give the database a new name in the script as two with the same name could be a problem.

Once you rin the script you have a copy of the original database but no data.

Next you right click and export the data into the new database. As the data loads new Index allocation Map files are generated.

Run dbcheck against the new database.

select your row counts in the two databases to ensure all the data came over.

Create a backup of the new database.

Restore the corrupt database from the new database backup.

Run dbcheck again and you should be good to go.

This is the only way I could figure out to fix the problem.

Kirk.wilson SSC Veteran Points: 268 More actions · Answer 4

You right click the database and select generate scripts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You don't need to manually rebuild indexes or to script out and recreate the database. The latter is only indicated in cases where CheckDB can't repair the damage.

This is one of the few cases where, even though repair allow data loss is required, there probably won't be data loss. It's errors in the allocation structures and checkDB will repair it entirely.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kirk.wilson SSC Veteran Points: 268 More actions · Answer 6

Understood but in my case the repair did not fix the problem.

BTW I found your posts very helpful, thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Kirk.wilson (6/5/2013)
Understood but in my case the repair did not fix the problem.

In that case, yes, script and recreate (or restore backup), but in the OP's case CheckDB did fix the error, so there's no need for all the work of recreating the DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

talk_tina4610 SSC-Addicted Points: 459 More actions · Answer 8

Hi Gail,

Thanks for your post. That was a saver post, I appreciate.

I can now go on and run same process in the production DB.

Thanks Wilson.

Best regards,

Tina

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Take a backup first. Just in case.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass