August 31, 2012 at 1:40 pm
Hi guys,
one of my customer's hard drive failed. They were able to safe most of the data and create bit copy of the failed disk but few corrupted sectors were at MDF file. And guess what ... 'we have no backups'. So I run DBCC checkdb and it reported corruption of clustered index, tried to run dbcc checktable (alldocs,repair_allow_data_loss) and got this
DBCC results for 'AllDocs'.
Repair: The Nonclustered index successfully rebuilt for the object "dbo.AllDocs, Docs_IdLevelUnique" in database "WSS_Content_04a315e0-42c4-40d5-a432-65590fe560b0".
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 69575286, index ID 1 will be rebuilt.
Could not repair this error.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 69575286, index ID 1, partition ID 72057608226996224, alloc unit ID 72057608630173696 (type In-row data). Page (1:2728879) was not seen in the scan although its parent (1:2723806) and previous (1:2728878) refer to it. Check any previous errors.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 69575286, index ID 1, partition ID 72057608226996224, alloc unit ID 72057608630173696 (type In-row data). Page (1:2728896) is missing a reference from previous page (1:2728879). Possible chain linkage problem.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 69575286, index ID 2 will be rebuilt.
The error has been repaired.
There are 51682 rows in 3233 pages for object "AllDocs".
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'AllDocs' (object ID 69575286).
CHECKTABLE fixed 0 allocation errors and 2 consistency errors in table 'AllDocs' (object ID 69575286).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I think I ran out of options ... any thoughts? :w00t:
August 31, 2012 at 2:01 pm
Please run the following and post the full and complete output.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
August 31, 2012 at 2:12 pm
Thank you for your help, here you are:
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:102602) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057608649768960 (type Unknown), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 69575286, index ID 1, partition ID 72057608226996224, alloc unit ID 72057608630173696 (type In-row data). Page (1:2728879) was not seen in the scan although its parent (1:2723806) and previous (1:2728878) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 69575286, index ID 1, partition ID 72057608226996224, alloc unit ID 72057608630173696 (type In-row data). Page (1:2728896) is missing a reference from previous page (1:2728879). Possible chain linkage problem.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:1293) in object ID 69575286, index ID 1, partition ID 72057608245936128, alloc unit ID 72057594043760640 (type LOB data), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 2 consistency errors in table 'AllDocs' (object ID 69575286).
CHECKDB found 2 allocation errors and 2 consistency errors in database 'WSS_Content_04a315e0-42c4-40d5-a432-65590fe560b0'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (WSS_Content_04a315e0-42c4-40d5-a432-65590fe560b0).
August 31, 2012 at 2:31 pm
Hell, a sharepoint DB...
Because of the way sharepoint stores the structure of the site in the tables, repairing a sharepoint DB with repair_allow_data_loss can result in an unusable site.
You did take a backup before starting to repair?
I'd honestly suggest restore that pre-repair backup, see if sharepoint will load and then export any and all libraries from sharepoint that will export and then recreate the site.
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
August 31, 2012 at 3:13 pm
Did you try re-creating the indexes on the table?
August 31, 2012 at 3:22 pm
Are you thinking of a nonclustered index? I think index ID 1 means it is the clustered index, i.e. the actual pages making up the table so the only way to recover from that is to restore from backup or do a repair with data loss, but that may invalidate the database from SharePoint's perspective as Gail has said.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 31, 2012 at 3:56 pm
Nalini D (8/31/2012)
Did you try re-creating the indexes on the table?
Won't work. The repair fixed the damage to the nonclustered index and some damage to the clustered index, but the remaining is also in the clustered index. There's no duplicate copy of the data to work from.
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 3, 2012 at 4:31 am
Thanks everybody for their time.
Luckily with my colleague (SharePoint geek) we were able to salvage document streams, their name, path and version... Well it costs us many hours of work which would not be necessary JUST if there would be water-tight backup. (There were NONE!) MDF had +/- 20GB, LDF 300 GB so guess what ...
Do backup, monitor backup, verify backup .. easy 😉
September 3, 2012 at 6:21 am
re-create indexes for those tables.
September 3, 2012 at 7:07 am
arunkumarco (9/3/2012)
re-create indexes for those tables.
GilaMonster (8/31/2012)
Nalini D (8/31/2012)
Did you try re-creating the indexes on the table?Won't work. The repair fixed the damage to the nonclustered index and some damage to the clustered index, but the remaining is also in the clustered index. There's no duplicate copy of the data to work from.
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 3, 2012 at 7:09 am
sqlmate (9/3/2012)
Well it costs us many hours of work which would not be necessary JUST if there would be water-tight backup. (There were NONE!) MDF had +/- 20GB, LDF 300 GB so guess what ...
You'll be amazed (or horrified) how many people post here with corrupt databases and either very old backups or no backups at all. 🙁
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 3, 2012 at 2:27 pm
Well I can imagine that ... it's the same old story all over again ... I always think of this statement: 'there are two groups of people; the one who do backups and the one who will' 🙂
December 17, 2012 at 11:47 pm
SQL databases or MDF files of Live Exchange Server can be damaged due to accidental deletion of some tables from server or slow running of SQL Server. To repair corrupt MDF files from crashed SQL Server, one can use RecoveryFix for SQL database. It recovers data in two modes i.e., Live SQL mode and Batch mode. The software supports recovery of Database objects from SQL Server 2000, 2005 and 2008 versions.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply