September 11, 2012 at 1:30 am
Another thing i would do with some urgency is to audit all user databases and find which ones are set to simple recovery and then query the business to have them confirm possible data loss is acceptable for the databases you find (if any)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 11, 2012 at 2:49 am
Perry Whittle (9/11/2012)
Another thing i would do with some urgency is to audit all user databases and find which ones are set to simple recovery and then query the business to have them confirm possible data loss is acceptable for the databases you find (if any)
And set up regular scheduled consistency checks.
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
September 11, 2012 at 12:23 pm
That is a good idea. I need to let them know the potential danger in keeping them in simple recovery.
Here is the last dbcc
Msg 8905, Level 16, State 1, Line 1
Extent (3:2060072) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2061480) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2062544) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2062896) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2063952) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2065016) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:443009) is pointed to by the next pointer of IAM page (3:46916) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:443009) is pointed to by the previous pointer of IAM page (3:56127) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan.
CHECKDB found 8 allocation errors and 0 consistency errors not associated with any single object.
Msg 8910, Level 16, State 1, Line 1
Page (3:441883) in database ID 5 is allocated to both object ID 46935539, index ID 1, partition ID 72057615824584704, alloc unit ID 72057617649303552 (type In-row data), and object ID 1548844880, index ID 1, partition ID 72057615819341824, alloc unit ID 72057617643536384 (type In-row data).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'RPT_Mrln_Progress_Pymt_Detail' (object ID 46935539).
Msg 8948, Level 16, State 1, Line 1
Database error: Page (3:442994) is marked with the wrong type in PFS page (3:436752). PFS status 0x0 expected 0x60.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'syssubscriptions' (object ID 603461524).
Msg 8906, Level 16, State 1, Line 1
Page (3:435329) in database ID 5 is allocated in the SGAM (3:3) and PFS (3:428664), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:435448) is pointed to by the next pointer of IAM page (0:0) in object ID 987202617, index ID 1, partition ID 72057615804334080, alloc unit ID 72057617626955776 (type In-row data), but it was not detected in the scan.
CHECKDB found 2 allocation errors and 0 consistency errors in table 'ENTITY_MATCH_CRITERIA_corrupt' (object ID 987202617).
Msg 8948, Level 16, State 1, Line 1
Database error: Page (3:443004) is marked with the wrong type in PFS page (3:436752). PFS status 0x0 expected 0x60.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'ALERT_HISTORY' (object ID 1380304077).
CHECKDB found 13 allocation errors and 0 consistency errors in database 'Marlin'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Marlin).
September 11, 2012 at 2:46 pm
Thanks Perry.
How important are these messages in the checkdb results?
The Index Allocation Map (IAM) page (3:443009) is pointed to by the next pointer of IAM page (3:46916) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:443009) is pointed to by the previous pointer of IAM page (3:56127) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan.
CHECKDB found 8 allocation errors and 0 consistency errors not associated with any single object.
Msg 8910, Level 16, State 1, Line 1
Page (3:441883) in database ID 5 is allocated to both object ID 46935539, index ID 1, partition ID 72057615824584704, alloc unit ID 72057617649303552 (type In-row data), and object ID 1548844880, index ID 1, partition ID 72057615819341824, alloc unit ID 72057617643536384 (type In-row data).
September 11, 2012 at 3:19 pm
jdbrown239 (9/11/2012)
Thanks Perry.How important are these messages in the checkdb results?
Perry Whittle (9/10/2012)
Personally, i would revert to the clean backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 11, 2012 at 3:44 pm
Well reverting to the last good backup we be a loss of more than a weeks data. Gail said that running the checkdb with repair I could lose anywhere from 0-91 pages. Since these allocation errors seem to be in indexes I think the best bet would be to run the repair and then rebuild any indexes that have to much loss. What do you think...any one?
September 11, 2012 at 3:53 pm
You need to remember that indexid = 1 is the clustered index, which means the table and the data itself. You can lose data and you need to be prepared for that.
September 11, 2012 at 4:09 pm
Lynn Pettis (9/11/2012)
You need to remember that indexid = 1 is the clustered index, which means the table and the data itself. You can lose data and you need to be prepared for that.
+1 😉
The pages are not consecutive either, so there will likely be gaps across the data.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 11, 2012 at 4:16 pm
If you run repair, you will lose data, hard to tell what (I can't remember how CheckDB handles cases where a page is allocated to two tables). It may result in relational inconsistencies (child rows without matching parent rows), it may result in transactional inconsistencies, repair doesn't consider anything other than the physical structure of the DB when repairing.
You won't need to rebuild indexes after repair. When the damage is in the nonclustered indexes, the repair rebuilds them. When the damage is in the base table (as in your case) repair will ensure that the nonclustered indexes are correct afterwards (by discarding any data from them that matches data discarded from the clustered index)
You can revert to the backup, in which case you'll know that the DB is consistent and accurate as of the point that the backup was taken, or you can repair and hope that the data loss isn't in critical places that will result in incorrect reports or application data.
This looks like a financial database, consider that repair could, for example, discard pages that detail payments made while not touching stored balances.
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
September 13, 2012 at 8:07 am
Thanks you guys for all of your help. It really helped to give me a good picture of the situation and my options.
Doug
September 17, 2012 at 8:19 am
Hi Everyone
I was able to copy the data from the corrupt table into another table the rename the corrupt table then name the new table the original name and create the indexes. After a checkdb only the corrupt table shows up not the new one.
This issue I do have is when I try to drop the corrupt table I get an error:
Msg 5243, Level 22, State 1, Line 3
An inconsistency was detected during an internal operation.
When I try to delete the data from the table to then try and drop it I get this error:
The query processor is unable to produce a plan because the index 'PK_ENTITY_MATCH_CRITERIA_corrupt' on table or view 'ENTITY_MATCH_CRITERIA_corrupt' is disabled.
When I try to rebuild the index to enable it i get the error:
Msg 5243, Level 22, State 1, Line 1
An inconsistency was detected during an internal operation.
When I try to drop the index I get the error:
Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.
Msg 5243, Level 22, State 1, Line 3
An inconsistency was detected during an internal operation.
Any Ideas?
September 17, 2012 at 9:22 am
First idea, that's not a way to fix corruption...
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Full output.
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
September 17, 2012 at 10:05 am
Here is the full output.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2060072) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2061480) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2062544) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2062896) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2063952) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (3:2065016) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:443009) is pointed to by the next pointer of IAM page (3:46916) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:443009) is pointed to by the previous pointer of IAM page (3:56127) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan.
CHECKDB found 8 allocation errors and 0 consistency errors not associated with any single object.
Msg 8906, Level 16, State 1, Line 1
Page (3:435329) in database ID 5 is allocated in the SGAM (3:3) and PFS (3:428664), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:435448) is pointed to by the next pointer of IAM page (0:0) in object ID 987202617, index ID 1, partition ID 72057615804334080, alloc unit ID 72057617626955776 (type In-row data), but it was not detected in the scan.
CHECKDB found 2 allocation errors and 0 consistency errors in table 'ENTITY_MATCH_CRITERIA_corrupt' (object ID 987202617).
Msg 8948, Level 16, State 1, Line 1
Database error: Page (3:443004) is marked with the wrong type in PFS page (3:436752). PFS status 0x0 expected 0x60.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'DOCUMENT_CHECKLIST' (object ID 1239779574).
CHECKDB found 11 allocation errors and 0 consistency errors in database 'Marlin'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Marlin).
September 17, 2012 at 10:08 am
My advice from earlier stands. Repair or restore.
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
September 17, 2012 at 11:23 am
Restore if I were you at least you have a consistent start point, the data can always be re entered.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply