Need to avoid a DBCC REPAIR_ALLOW_DATA_LOSS if possible

Question

Need to avoid a DBCC REPAIR_ALLOW_DATA_LOSS if possible

Dana Eddy-486208

SSC Journeyman

Points: 78
More actions
September 12, 2007 at 8:02 am

#176582

It’s time to ask a GURU for advice… I am NOT a DBA! I know enough to know what I don’t know… What would you do in this case?

As found: A production SharePoint database, needed 24/7, (approx 16GB), large amount of daily changes. Backups have been running fine, Full, Diffs and trans. When trying to build a dev environment from the production (restore db’s to dev server) the site db failed to restore. (Not overly concerned because also have SharePoint site b/u’s that will restore&nbsp. BTW, the portal is not experiencing any impact from these errors. There is no evidence of problem outside the dbcc checkdb.

Action taken:
1)Through script, took sql service offline (o-dark thirty), copied .mdb & .ldf to dev server brought services back on-line
2) attached .mdb anf .ldf to dev sql server
3) On dev, ran dbcc checkdb(‘portal_site’) .. errors resulted
4) ran alter database… set single user… with rollback immediate… dbcc checkdb('portal_site', repair_fast) .. did not fix
5) ran DBCC CHECKDB ('portal_site', REPAIR_REBUILD) .. did not fix

I want to avoid the REPAIR_ALLOW_DATA_LOSS if possible… duh? (But again, shouldn’t be a big deal because of the SP B/U’s. Just call me ckicken!!)
Here are the results of a checkdb with no_infomsgs, all errormsgs (after all the above)

Msg 8928, Level 16, State 1, Line 1
Object ID 501576825, index ID 1, partition ID 72057594158907392, alloc unit ID 72057594170376192 (type In-row data): Page (1:1594118) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 501576825, index ID 1, partition ID 72057594158907392, alloc unit ID 72057594170376192 (type In-row data). Page (1:1594118) was not seen in the scan although its parent (1:1727065) and previous (1:1594113) refer to it. Check any previous errors.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 501576825, index ID 1, partition ID 72057594158907392, alloc unit ID 72057594170376192 (type In-row data), page (1:1594118), row 5. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 841 and 881.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 501576825, index ID 1, partition ID 72057594158907392, alloc unit ID 72057594170376192 (type In-row data), page (1:1594118), row 5. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 841 and 881.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 501576825, index ID 1, partition ID 72057594158907392, alloc unit ID 72057594170376192 (type In-row data). Page (1:1599485) is missing a reference from previous page (1:1594118). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'UserData' (object ID 501576825).
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:266403) in object ID 1993058136, index ID 1, partition ID 72057594163363840, alloc unit ID 71906736119218176 (type LOB data). Expected value 100_PCT_FULL, actual value INVALID_FREE_SPACE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'Docs' (object ID 1993058136).
CHECKDB found 0 allocation errors and 6 consistency errors in database 'PORTAL_SITE'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PORTAL_SITE).

My thought is to run the REPAIR_ALLOW_DATA_LOSS and then fix any side effects with a SharePoint site restore (spsadm)
Are there any options I’m not thinking of?
(As for a backup restore... not an option because 1) the current b/u's will not restore and 2) old b/u's are stale)

Next question, any suggested maintenance scripts on site that will prevent a reoccurrence? (ie. Send mail if error or best practice threshold exceeded)

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

You should restore from your backups. As the output from CHECKDB clearly states, REPAIR_ALLOW_DATA_LOSS is what's needed to repair these errors so its no surprise that none of the other repair options work (in fact, REPAIR_FAST doesn't do anything - I only left it in there for 2005 for backwards-compatibility). If you choose to run repair instead of restoring from backups, you will lose data from that table.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

ianT SSCrazy Points: 2956 More actions · Answer 2

"(Not overly concerned because also have SharePoint site b/u’s that will restore...."

Did you run checkdb on these backups and if so was it OK?

Dana Eddy-486208 SSC Journeyman Points: 78 More actions · Answer 3

The SharePoint backups are not SQL backups (although they are backing up data from the SQL databases) and they do restore. What I have done is run the repair_allow_data_loss and then restored from SharePoint on-top of that. (Less work than a restore from the Sharepoint B/U's)

Does anyone have recommendations for an appropraite maintenance script/plan for SharePoint db's? (other than just adding checksums to the b/u)

Thank you for the feeback,