SQL 2005 / Sharepoint Database Corruption

Question

Post reply

SQL 2005 / Sharepoint Database Corruption

thomas.jreige

SSC Rookie

Points: 25
More actions
January 13, 2009 at 6:44 pm

#198475

Hi Everyone,
I am looking at my event logs and found this one that is of particular interest to me.
BackupIoRequest::WaitForIoCompletion: read failure on backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\WSS_Content.mdf'. Operating system error 23(Data error (cyclic redundancy check).).
Event ID 18210
Has anyone seen thsi before and is there any way of recovering this file. I know there is an small block error issue on my hdd but want to recovery this file in the event of something going down.
Thanks so much.
Tom.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you please connect to that server from a querying tool and run the following. Post all of the outputs

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thomas.jreige SSC Rookie Points: 25 More actions · Answer 2

Hi Gail,

Thanks for your reply,

I am running this DB Check now and will get back to you soon with some output.

Tom.

thomas.jreige SSC Rookie Points: 25 More actions · Answer 3

Hi Gail,

Here is the output of the command. This database server is used as the database server for Sharepoint.

Thanks so much.

Msg 8966, Level 16, State 2, Line 1

Unable to read and latch page (1:200220) with latch type SH. 23(Data error (cyclic redundancy check).) failed.

CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

Msg 8929, Level 16, State 1, Line 1

Object ID 277576027, index ID 1, partition ID 72057594038714368, alloc unit ID 72057594043760640 (type In-row data): Errors found in off-row data with ID 1574305792 owned by data record identified by RID = (1:1006691:19)

Msg 2533, Level 16, State 1, Line 1

Table error: page (1:200220) allocated to object ID 277576027, index ID 1, partition ID 72057594038714368, alloc unit ID 72057594043826176 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Msg 8965, Level 16, State 1, Line 1

Table error: Object ID 277576027, index ID 1, partition ID 72057594038714368, alloc unit ID 72057594043826176 (type LOB data). The off-row data node at page (1:200220), slot 0, text ID 1574305792 is referenced by page (1:199451), slot 0, but was not seen in the scan.

CHECKDB found 0 allocation errors and 3 consistency errors in table 'AllDocStreams' (object ID 277576027).

CHECKDB found 0 allocation errors and 4 consistency errors in database 'WSS_Content'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (WSS_Content).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Ouch.

Firstly, there's a hardware error. You may want to check and even replace the drives. Personally I wouldn't be ahppy about putting a database back on shaky hardware.

There's a single page of the database damaged. The bad news is that it's a data page, in fact it's one of the LOB pages (Large Object). That means, in the context of sharepoint, it's probably part of a document.

Do you have a clean, up-to-date backup for this database?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thomas.jreige SSC Rookie Points: 25 More actions · Answer 5

Hi, Yes I do. Not up to date but will do.

It is from the sharepoint backup.

Thanks.

TOm.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

How much will you lose by restoring the backup? This is repairable, but you're going to lose what's probably a document. Not easy to say which one.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thomas.jreige SSC Rookie Points: 25 More actions · Answer 7

If it is one document, i can lose that. Not worries.

Where do i go from here?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Take the database into single user mode. That means that no one can access it for the duration of the fix. You may want to shut down sharepoint (if that's possible)

ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Then run CheckDB with the repair option. This may take a while.

DBCC CHECKDB ('WSS_Content') WITH REPAIR_ALLOW_DATA_LOSS

Once it's finished (if it succeeds) I would strongly recommend that you run the checkDB command I gave earlier, to make sure that things are OK. Then you can take the database back to multi_user and bring sharepoint back up

I would also recommend that you consider moving that database to another drive. There's a damaged portion of the drive within the database file. It's not going to go away and will very likely cause more problems in the future.

In addition, I would recommend that you schedule the checkDB command that I gave earlier in a job to run weekly. The earlier corruption is found, the easier it is to fix.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PAram-383796 SSC Enthusiast Points: 127 More actions · Answer 9

Run this command first

ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Select Object_name(277576027)

Take that object name and run this command

DBCC CHECKTABLE (<

>) WITH REPAIR_ALLOW_DATA_LOSS