asked
Extent (7:1145888) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Hi, How to resolve the below issue? I have done RebuildIndex but the problem not solved.I think page restore will resolve the below issue but,I could not suppose to do.Can You tell me the steps to resolve the below Issue? Problem: Executing the query "DBCC CHECKDB(N'TestDB') WITH NO_INFOMSGS " failed with the following error: "Extent (7:1145864) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Extent (7:1145888) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Extent (7:1146000) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Extent (7:1146080) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. CHECKDB found 4 allocation errors and 0 consistency errors not associated with any single object. CHECKDB found 4 allocation errors and 0 consistency errors in database 'TestDB'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TestDB).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
Is it a live production DB "TestDB" and recovery model is full?....if not then either DBCC with repair_allow_data_loss option OR a restore from last known valid backup could help the cause... Otherwise....If you have a sound backup plan (full, differential, log backups), then your best bet is Point in time recovery.... But for that first you would need to sort out the last valid full backup...you can do so by restoring the full backups in sequence (descending OR RANDOM) on a Test server and running DBCC on them.... When you find the valid backup...then you can try the Log backups from there onwards to keep data loss to minimum... Also, you can also try restoring a fresh backup on a Test server and running DBCC with repair_allow_data_loss option to see how much data is lost...then you can decide accordingly Following links should be helpful in this regard [Restore a SQL Server Database to a Point in Time (Full Recovery Model)][1] [Tail-Log Backups][2] [1]:
http://technet.microsoft.com/en-us/library/ms179451.aspx [2]:
http://technet.microsoft.com/en-us/library/ms179314.aspx
Hi Sacred, I have tryied with DBCC with repair_allow_data_loss but the problem was not solved.The database was in full recovery model only. But My plan is Cant we resolve this issue with out Backup Plan??
There is no other alternative I know...But if you find any I would definitely recommend not to try that...Also make sure you would be supported by MS after following that alternative.
Sacred, Allocation Errors- Not required Backup restore Consistance Errors-required Backup restore Can you comment on the above two things.I feel for Allocation Errors i.e NO DATA LOSS PHYSICALLY AND ERRORS DUE TO PROBLEMS ON INDEXES not required point in time recovery
As I said earlier the two supported options are repair_allow_data_loss or restore of backups...you should not try to fix system tables yourself.. But if the allocation errors got decreased after running DBCC with repair_allow_data_loss, then you can try running it number of times and see it may resolve the issue without backups....but I would recommend doing it on test server first
As @SacredJewel mentions, this is a serious corruption issue. The only way that I know how to fix it would be to physically write/alter the pages at the byte level and there are only a handful of people (<5?) that can probably do it. In other words, if this is a production database/server, don't attempt this. If REPAIR_ALLOW_DATA_LOSS didn't fix it, then restoring is going to be your best bet. I would recommend at the very least that you take a backup of the database (in it's current state) with CONTINUE_AFTER_ERROR (as mentioned here -->
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6402be8d-2a21-4c5f-a540-1f5c4c81f634/dbcc-checkdb-error-extent-in-database-is-marked-allocated-in-the-gam-but-no-sgam-or-iam-has?forum=sqldatabaseengine) to see if you can get a backup. This would allow you to at least start over in case something really goes south. Hope this helps!
17 People are following this question.
