May 27, 2015 at 6:58 am
Hi all
We've recently detected corruption in one of our more important SharePoint databases and have discovered that we were only running Simple backups on the database each night. We are not certain when exactly the issue occurred but suspect it was a week ago.
I'll put the full DBCC output below but the basic jist is below:
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373408), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
After doing a bit of poking around using the undocumented sys.fn_PhysLocFormatter (%%physloc%%) function I've narrowed down the problem to the off row data segment being used to store 2 word documents. The word documents themselves are not overly critical and it would not be a great loss if we couldnt get them back.
SharePoint seems happy enough to interact with the documents (I can download them for example) although they are both unreadable
Simple question - given our lack of a decent log backup (or actual knowledge of when the corruption occurred), would it be sensible to just try and delete the two problem documents and rerun checkdb to see if SQL Server managed to sort the issue simply by deleting the two problem rows? I would be hoping that SQL Server just deletes the rows and deallocates the problem off row space.
Not sure if I would just be storing up trouble for later though or whether that approach is destined to fail
Thanks in advance
Simon
PS: Full Check DB below
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373408), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373409), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373410), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373411), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373412), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373413), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373414), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594040549376 (type LOB data). The off-row data node at page (1:373415), slot 0, text ID 333774848 does not match its reference from page (1:46982), slot 0.
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594094747648 (type In-row data): Errors found in off-row data with ID 1311506432 owned by data record identified by RID = (1:2277271:5)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594081902592, alloc unit ID 72057594094747648 (type In-row data): Errors found in off-row data with ID 333774848 owned by data record identified by RID = (1:2282679:0)
CHECKDB found 0 allocation errors and 10 consistency errors in table 'AllDocStreams' (object ID 373576369).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'WSS_Content_CustomerDocs'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (WSS_Content_CustomerDocs).
May 27, 2015 at 7:16 am
You can try to delete the documents in question, though I suspect that will throw an error.
If you don't care about losing those two documents, then when you can take the system offline for some time, stop Sharepoint, take the DB into single user mode and run CheckTable against the AllDocStreams table with REPAIR_ALLOW_DATA_LOSS. It'll deallocate the broken LOB pages and delete the rows that reference them.
Do take a backup first, of course, and check before and after to ensure that only two rows are removed. If those are the only CheckDB errors then it should just be two rows deleted.
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
May 27, 2015 at 7:26 am
Hi Gail
Thanks for the quick reply.
Got lucky - after taking backups I just thought I'd take a punt on deleting the documents. After deleting the documents from both the Site and then Site collection recycle bins those two rows are gone and CHECK DB is returning fine so it looks like its sorted itself out.
Has highlighted a flaw in my maintenance checks. We run CHECK DB on most of our databases every night so we can jump on any issues quickly - for some reason that DB was missing from the plan so it left us a little exposed.
Thanks again for the assist
S
May 28, 2015 at 12:29 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply