Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC CHECKDB with allocation error


DBCC CHECKDB with allocation error

Author
Message
talk_tina4610
talk_tina4610
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
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.
Kirk.wilson
Kirk.wilson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
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
talk_tina4610
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
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
Kirk.wilson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
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
Kirk.wilson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
You right click the database and select generate scripts.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47359 Visits: 44392
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
Kirk.wilson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
Understood but in my case the repair did not fix the problem.

BTW I found your posts very helpful, thank you
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47359 Visits: 44392
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
talk_tina4610
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47359 Visits: 44392
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search