Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DBCC CHECKDB with allocation error Expand / Collapse
Author
Message
Posted Tuesday, June 4, 2013 10:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 10:29 AM
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.
Post #1459859
Posted Wednesday, June 5, 2013 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 12:52 PM
Points: 4, Visits: 15
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.
Post #1460224
Posted Wednesday, June 5, 2013 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 10:29 AM
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
Post #1460335
Posted Wednesday, June 5, 2013 12:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 12:52 PM
Points: 4, Visits: 15
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.
Post #1460374
Posted Wednesday, June 5, 2013 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 12:52 PM
Points: 4, Visits: 15
You right click the database and select generate scripts.
Post #1460379
Posted Wednesday, June 5, 2013 1:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1460418
Posted Wednesday, June 5, 2013 1:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 12:52 PM
Points: 4, Visits: 15
Understood but in my case the repair did not fix the problem.

BTW I found your posts very helpful, thank you
Post #1460427
Posted Wednesday, June 5, 2013 2:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1460441
Posted Thursday, June 6, 2013 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 10:29 AM
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
Post #1460558
Posted Thursday, June 6, 2013 2:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
Take a backup first. Just in case.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1460570
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse