November 29, 2016 at 5:04 pm
I've identified one of our databases has significant corruption, including several extent allocation issues and SGAM allocation issues.
After doing some research on this, I'm aware I can identify which tables/indexes are affected by the errors that give an object_id, however I'm unsure how to approach those SGAM and extent errors.
Is there any way to identify which tables and rows have been affected by these errors? Also, would the best direction to resolving this be restoring the last clean backup and manually updating the old backup (that backup is around 6 months old)?
Below is the DBCC CHECKDB results
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:16176) to (1:24263). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:24264) to (1:32351). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:80880) to (1:88967). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:88968) to (1:97055). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:97056) to (1:105143). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:105144) to (1:113231). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:113232) to (1:121319). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:129408) to (1:137495). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:137496) to (1:145583). See other errors for cause.
Msg 8948, Level 16, State 3, Line 1
Database error: Page (1:15000) is marked with the wrong type in PFS page (1:8088). PFS status 0x70 expected 0x60.
Msg 8948, Level 16, State 3, Line 1
Database error: Page (1:15001) is marked with the wrong type in PFS page (1:8088). PFS status 0x70 expected 0x60.
Msg 8948, Level 16, State 3, Line 1
Database error: Page (1:15002) is marked with the wrong type in PFS page (1:8088). PFS status 0x70 expected 0x60.
Msg 8948, Level 16, State 3, Line 1
Database error: Page (1:15003) is marked with the wrong type in PFS page (1:8088). PFS status 0x70 expected 0x60.
Msg 8948, Level 16, State 3, Line 1
Database error: Page (1:15004) is marked with the wrong type in PFS page (1:8088). PFS status 0x70 expected 0x60.
Msg 8948, Level 16, State 3, Line 1
Database error: Page (1:15005) is marked with the wrong type in PFS page (1:8088). PFS status 0x70 expected 0x60.
Msg 8948, Level 16, State 3, Line 1
Database error: Page (1:15006) is marked with the wrong type in PFS page (1:8088). PFS status 0x70 expected 0x60.
Msg 8928, Level 16, State 6, Line 1
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:15007) could not be processed. See other errors for details.
Msg 8905, Level 16, State 1, Line 1
Extent (1:14992) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:41496) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:51600) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:52288) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:52296) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:52320) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:52328) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:52336) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:67240) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:122136) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:172728) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:175120) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:189648) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:193880) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:214456) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:231088) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:232688) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:234536) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:249832) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:282208) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:310368) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:335096) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:360488) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368024) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368032) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368040) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368048) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368056) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368064) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368072) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368080) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:368088) in database ID 52 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 49 allocation errors and 0 consistency errors not associated with any single object.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15003) is pointed to by the next pointer of IAM page (0:0) in object ID 290100074, index ID 1, partition ID 72057594073645056, alloc unit ID 72057594087473152 (type In-row data), but it was not detected in the scan.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15004) is pointed to by the next pointer of IAM page (0:0) in object ID 290100074, index ID 2, partition ID 72057594073710592, alloc unit ID 72057594087538688 (type In-row data), but it was not detected in the scan.
CHECKDB found 2 allocation errors and 0 consistency errors in table 'NameValuePair_Latin1_General_CI_AS' (object ID 290100074).
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15000) is pointed to by the next pointer of IAM page (0:0) in object ID 629577281, index ID 4, partition ID 72057594068402176, alloc unit ID 72057594081837056 (type In-row data), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'AllDocs' (object ID 629577281).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 862626116, index ID 1, partition ID 72057594081968128, alloc unit ID 72057594097106944 (type In-row data), page ID (1:105144) contains an incorrect page ID in its page header. The PageId in the page header = (1:779064).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 862626116, index ID 1, partition ID 72057594081968128, alloc unit ID 72057594097106944 (type In-row data), page ID (1:105144) contains an incorrect page ID in its page header. The PageId in the page header = (1:779064).
CHECKDB found 1 allocation errors and 1 consistency errors in table 'AllDocVersions' (object ID 862626116).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594082230272, alloc unit ID 72057594055688192 (type LOB data), page ID (1:88968) contains an incorrect page ID in its page header. The PageId in the page header = (1:5710344).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594082230272, alloc unit ID 72057594055688192 (type LOB data), page ID (1:97056) contains an incorrect page ID in its page header. The PageId in the page header = (1:681376).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594082230272, alloc unit ID 72057594055688192 (type LOB data), page ID (1:113232) contains an incorrect page ID in its page header. The PageId in the page header = (1:787152).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594082230272, alloc unit ID 72057594055688192 (type LOB data), page ID (1:129408) contains an incorrect page ID in its page header. The PageId in the page header = (1:6544128).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594082230272, alloc unit ID 72057594055688192 (type LOB data), page ID (1:15007) contains an incorrect page ID in its page header. The PageId in the page header = (1:5713311).
Msg 8906, Level 16, State 1, Line 1
Page (1:15000) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8906, Level 16, State 1, Line 1
Page (1:15001) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8906, Level 16, State 1, Line 1
Page (1:15002) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8906, Level 16, State 1, Line 1
Page (1:15003) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8906, Level 16, State 1, Line 1
Page (1:15004) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8906, Level 16, State 1, Line 1
Page (1:15005) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8906, Level 16, State 1, Line 1
Page (1:15006) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8906, Level 16, State 1, Line 1
Page (1:15007) in database ID 52 is allocated in the SGAM (1:3) and PFS (1:8088), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
CHECKDB found 13 allocation errors and 0 consistency errors in table 'DocStreams' (object ID 926626344).
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15005) is pointed to by the next pointer of IAM page (0:0) in object ID 1202103323, index ID 1, partition ID 72057594077446144, alloc unit ID 72057594051690496 (type LOB data), but it was not detected in the scan.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15006) is pointed to by the next pointer of IAM page (0:0) in object ID 1202103323, index ID 1, partition ID 72057594077446144, alloc unit ID 72057594091667456 (type In-row data), but it was not detected in the scan.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15007) is pointed to by the next pointer of IAM page (0:0) in object ID 1202103323, index ID 2, partition ID 72057594077511680, alloc unit ID 72057594091798528 (type In-row data), but it was not detected in the scan.
CHECKDB found 3 allocation errors and 0 consistency errors in table 'WorkflowAssociation' (object ID 1202103323).
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15002) is pointed to by the next pointer of IAM page (0:0) in object ID 1253579504, index ID 1, partition ID 72057594068992000, alloc unit ID 72057594082623488 (type In-row data), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'TimerLock' (object ID 1253579504).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1454628225, index ID 1, partition ID 72057594083999744, alloc unit ID 72057594099662848 (type In-row data), page ID (1:16176) contains an incorrect page ID in its page header. The PageId in the page header = (1:5714480).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1454628225, index ID 1, partition ID 72057594083999744, alloc unit ID 72057594057457664 (type LOB data), page ID (1:24264) contains an incorrect page ID in its page header. The PageId in the page header = (1:5722568).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1454628225, index ID 1, partition ID 72057594083999744, alloc unit ID 72057594057457664 (type LOB data), page ID (1:80880) contains an incorrect page ID in its page header. The PageId in the page header = (1:5702256).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1454628225, index ID 1, partition ID 72057594083999744, alloc unit ID 72057594057457664 (type LOB data), page ID (1:137496) contains an incorrect page ID in its page header. The PageId in the page header = (1:6552216).
CHECKDB found 4 allocation errors and 0 consistency errors in table 'AuditData' (object ID 1454628225).
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:15001) is pointed to by the next pointer of IAM page (0:0) in object ID 1602104748, index ID 1, partition ID 72057594079281152, alloc unit ID 72057594053263360 (type LOB data), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'UI' (object ID 1602104748).
CHECKDB found 75 allocation errors and 1 consistency errors in database 'SPSM'.
How this came about responsibility-wise: there's no routine CHECKDB's happening and outside service providers perform server builds and routine maintenance.
November 29, 2016 at 5:23 pm
My advice is to get a hold of Gail Shaw (SQLintheWild) from here on the SSC.com forums and engage her as a consultant. She is in South Africa but you should be able to engage her services ASAP as she just mentioned yesterday about having some availability. She is extremely well versed in corruption issues and their recovery options.
If you think you need better then the number one resource in the world would be SQLSkills.com. Paul Randal (cofounder there) ran the team that literally wrote the SQL Server 2005 checkdb code. Everyone at that company is as good as they come (as is Gail).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 29, 2016 at 6:25 pm
I should have noted that this database isn't business critical. I definitely would look into hiring Gail if this were, thanks TheSQLGuru.
Is there anything more I could do towards fixing this, or identifying the corrupted data myself?
Andrew P.
November 29, 2016 at 6:52 pm
Andrew P (11/29/2016)
I should have noted that this database isn't business critical. I definitely would look into hiring Gail if this were, thanks TheSQLGuru.Is there anything more I could do towards fixing this, or identifying the corrupted data myself?
Andrew P.
I am pretty sure both Paul and Gail (and others) have blog posts or PASS Summit or other sessions available about how to go about deciphering checkdb output and recovering as best you can.
Good luck with it ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 29, 2016 at 8:29 pm
There's a PASS group on Disaster Recovery - this is amazing! Thanks for the pointer 🙂
November 29, 2016 at 10:05 pm
This was removed by the editor as SPAM
November 29, 2016 at 10:33 pm
This was removed by the editor as SPAM
November 30, 2016 at 1:50 am
Andrew P (11/29/2016)
I've identified one of our databases has significant corruption, including several extent allocation issues and SGAM allocation issues.After doing some research on this, I'm aware I can identify which tables/indexes are affected by the errors that give an object_id, however I'm unsure how to approach those SGAM and extent errors.
Is there any way to identify which tables and rows have been affected by these errors? Also, would the best direction to resolving this be restoring the last clean backup and manually updating the old backup (that backup is around 6 months old)?
First of all run this command and post the full and unedited output
DBCC CheckDB ('SPSM') WITH NO_INFOMSGS, ALL_ERRORMSGS
😎
Next step is to check if the data can be accessed, reccomend that you simply do a select into from the tables listed in the error output, i.e.
SELECT
*
INTO [anotherdb].dbo.tmp_AllDocs
FROM AllDocs
There are 8 tables listed (I may have missed some)
NameValuePair_Latin1_General_CI_AS (object ID 290100074)
AllDocs (object ID 629577281)
AllDocVersions (object ID 862626116)
DocStreams (object ID 926626344)
WorkflowAssociation (object ID 1202103323)
TimerLock (object ID 1253579504)
AuditData (object ID 1454628225)
UI (object ID 1602104748)
Question, what errors are you seeing in the SQL Error Log?
EXEC sp_readerrorlog
November 30, 2016 at 6:00 am
It's not repairable (there's no minimum level to repair line at the end of the output), so your only option here is to restore the last good backup and accept the data loss (short of very time-consuming patching together of database tables).
Running CheckDB with any repair level will just be a waste of time.
As for identifying what tables are involved, Eirikur has identified 8, there may be more due to the error
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 52 pages from (1:16176) to (1:24263). See other errors for cause.
Try querying each table in the DB. Some may fail, but most should query OK. If they do, you can use something like SQLDAtaCompare to sync the data from the damaged database back to the 6-month old restored copy
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
November 30, 2016 at 7:00 pm
sp_readerrorlogs doesn't show any issues except those from the times I've ran DBCC CHECKDB and a number of non-descript service exception stack dumps from a vendor-managed system which I will trace.
SELECT *
INTO [anotherdb].dbo.tmp_AllDocs
FROM AllDocs (as well as with ORDER BY AllDocs.ID DESC) gives no rows and cuts the connection with
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:132528; actual 1:6547248). It occurred during a read of page (1:132528) in database ID 52 at offset 0x00000040b60000 in file 'D:\MSSQL\SPSM.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I had forgotten to note that the cause of this corruption was found to be a faulty VM host, and since it was moved off that host six months ago it hasn't had additional corruption. Several other databases were migrated to another host as well, and these were "fixed" via REPAIR_ALLOW_DATA_LOSS, but are now coming back clean.
When I let the service provider who manages our server builds, software installs and database maintenance know there was corruption back in June/July they had no sense of urgency, ran REPAIR_ALLOW_DATA_LOSS over several of the corrupt databases on that host and considered that mission accomplished, and there's no scheduled CHECKDB's.
As this database isn't critical (the database has been corrupt for six months without user complaints and management have accepted the service provider's claim that "fixing the corruption would cause more problems than it would save") I'll leave this to the people whose responsibility it is, implement routine CHECKDB's myself, and upskill so I can handle DR myself better in future. Steve Stedman's PASS session has helped me gain a basic understanding of how to diagnose corruption.
Thanks heaps Gail and Eirikur, I appreciate having your guidance on the difficulty of repairing this.
Andrew P.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply