DBCC checkdb report error : marked allocated in the GAM, but no SGAM or IAM has allocated it.

Question

hi folks:
DBCC check reported error: marked allocated in the GAM, but no SGAM or IAM has allocated it.
and I've used dbcc check with repair_allow_data_loss . It did fix all allocation errors.

Question: Did I still lose any data ?

Thanks

Hui

Answer 1

part of dbcc result:
Extent (1:7643160) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
The error has been repaired.
Msg 8905, Level 16, State 1, Line 6
Extent (1:7651248) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
The error has been repaired.
Msg 8905, Level 16, State 1, Line 6
Extent (1:7659336) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
The error has been repaired.
Msg 8905, Level 16, State 1, Line 6
Extent (1:7667424) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
The error has been repaired.
CHECKDB found 127 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB fixed 127 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 127 allocation errors and 0 consistency errors in database 'DWH_LANDING'.
CHECKDB fixed 127 allocation errors and 0 consistency errors in database 'DWH_LANDING'.

Answer 2

Hi @Shi, Hui ,

The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup. Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. The REPAIR_ALLOW_DATA_LOSS option is not an alternative for restoring from a known good backup. It is an emergency "last resort" option recommended for use only if restoring from a backup is not possible.

More details please refer to the ''warning'' in following document:
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15#arguments

Answer 3

thanks Criszhan. This is the last resort we have at this moment. I've run this option and it says
it has corrected all errors. Question: can I say there is no data loss ???

Cheers

Answer 4

Question: Did I still lose any data ?

There is no GUARNTEED way to tell that, you would have to get your hands dirty and find it out by comparing. This is the biggest problem with checkdb with repair_allow_data_loss, if it finds a way to repair data it may go on deleting data without any restriction to get database in online consistent state. Ideally, since you are worried about data loss you should have restored from clean backup or would have tried backing the database with continue_after_errror clause and then restoring with same clause and running checkdb repair_allow_data_loss on this copy than on original database. (Although backing and restoring with continue_after_error clause does not guarantee a backup and restore of corrupt DB but there is no harm in trying.)

The other way is to test is, if you have recent backup restore it with different name and do data comparison.

Answer 5

I actually got different views from other threads. Some MVP says there is no data loss but no detailed info.

this
https://social.msdn.microsoft.com/Forums/en-US/45ca9c2d-8542-44f2-9bc8-4342cbca3a35/consistency-error?forum=sqlservermigration

and this

https://social.msdn.microsoft.com/Forums/en-US/220c30bd-4833-4913-8c02-42f8b5bed3e2/consistency-error?forum=sqldatabaseengine