CHECKDB: 97 consistency errors and none repaired

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

CHECKDB: 97 consistency errors and none repaired

Author

Topic

schoeppchen
Starting Member

3 Posts

Posted - 2006-05-11 : 08:15:46

Hi all,

I'm using WSUS2 to deploy software updates at my company, WSUS2 uses WMSDE (SQL Server Desktop Engine 2000 SP4) to store its database.

WSUS2 suddenly stopped working, I identified problems at the Database Log reading like this:

quote:

2006-05-11 12:17:52.64 spid54 Error: 7105, Severity: 22, State: 6
2006-05-11 12:17:52.64 spid54 Page (1:79), slot 29 for text, ntext, or image node does not exist..
[...]

So I ran a CHECKDB via osql on the commandline and got errors which should be repaired with a minimum of REPAIR_ALLOW_DATA_LOSS.

So I ran the following command on the corruprt Datebase 'SUSDB' after putting the DB into single user mode:


1> DBCC CHECKDB ('SUSDB',REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
2> GO

And I got the fowllowing output (condensed):

quote:

Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 131203072 owned by data record identified
by RID = (1:24:2) id = 1 and indid = 3.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 131268608 owned by data record identified
by RID = (1:24:6) id = 3 and indid = 2.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 131334144 owned by data record identified
by RID = (1:24:7) id = 4 and indid = 1.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 131399680 owned by data record identified
by RID = (1:24:8) id = 4 and indid = 2.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 131465216 owned by data record identified
by RID = (1:24:13) id = 10 and indid = 2.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 131530752 owned by data record identified
by RID = (1:24:15) id = 11 and indid = 1.

[... MANY MANY MORE OF THOSE FAILURES WITH DIFFERENT IDs ...]

Object ID 2: Errors found in text ID 459538432 owned by data record identified
by RID = (1:104:27) id = 1858105660 and indid = 3.
The repair level on the DBCC statement caused this repair to be
bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 459735040 owned by data record identified
by RID = (1:104:28) id = 1858105660 and indid = 4.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 4462411776 owned by data record identified
by RID = (1:107:5) id = 373576369 and indid = 6.
Msg 8929, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2: Errors found in text ID 327745536 owned by data record identified
by RID = (1:107:10) id = 487672785 and indid = 1.

[... MANY MANY MORE OF THOSE FAILURES WITH DIFFERENT IDs ...]

Msg 8964, Level 16, State 1, Server FILES\WSUS, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:64), slot
4, text ID 459735040 is not referenced.
Msg 8964, Level 16, State 1, Server FILES\WSUS, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:68), slot
7, text ID 197459968 is not referenced.
Msg 8964, Level 16, State 1, Server FILES\WSUS, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:69), slot
0, text ID 197263360 is not referenced.
Msg 8964, Level 16, State 1, Server FILES\WSUS, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:70), slot
0, text ID 4462411776 is not referenced.
Msg 8964, Level 16, State 1, Server FILES\WSUS, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:71), slot
5, text ID 197197824 is not referenced.
Msg 8928, Level 16, State 1, Server FILES\WSUS, Line 1
Object ID 2, index ID 255: Page (1:79) could not be processed. See other errors
for details.
Msg 8965, Level 16, State 1, Server FILES\WSUS, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:79), slot
0, text ID 327745536 is referenced by page (1:107), slot 10, but was not seen
in the scan.

[... MANY MANY MORE OF THOSE FAILURES WITH DIFFERENT IDs ...]

The repair level on the DBCC statement caused this repair to be
bypassed.
The repair level on the DBCC statement caused this repair to be
bypassed.
The repair level on the DBCC statement caused this repair to be
bypassed.
The repair level on the DBCC statement caused this repair to be
bypassed.
The repair level on the DBCC statement caused this repair to be
bypassed.
CHECKDB found 0 allocation errors and 97 consistency errors in table
'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 97 consistency errors in database
'SUSDB'.

None of the errors are repaired, what can I do to repair the Database?

Thanks for any hints!

http://www.linuxhelpforum.de

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 09:03:28

You may want to first make a backup (do NOT overwrite any existing files!) in case someone else recommends a method of rescuing any lost data. For extra safety you could also STOP SQL Services and COPY the MDF and LDF files to a "safe" location (preferably a different drive, or to a networked machine, in case the local drive is faulty)

1) Check the SQL Log and see if you have any I/O errors (like "Torn Page") - note the date/time of the first one

2) Check the Event Log and see if you have any hardware problems - in particular shortly before the date/time from Step (1)

2) Restore from backup

3) Check if the restored database is good: DBCC CHECKDB ('MyDatabase') WITH ALL_ERRORMSGS, NO_INFOMSGS

Kristen

schoeppchen
Starting Member

3 Posts

Posted - 2006-05-11 : 09:28:08

quote:
Originally posted by Kristen

You may want to first make a backup (do NOT overwrite any existing files!) in case someone else recommends a method of rescuing any lost data. For extra safety you could also STOP SQL Services and COPY the MDF and LDF files to a "safe" location (preferably a different drive, or to a networked machine, in case the local drive is faulty)

Already done that but thanks for the hint.

quote:
1) Check the SQL Log and see if you have any I/O errors (like "Torn Page") - note the date/time of the first one

No I/O errors reported in the logs.

quote:
2) Check the Event Log and see if you have any hardware problems - in particular shortly before the date/time from Step (1)

No hardware problem.

quote:
2) Restore from backup
3) Check if the restored database is good: DBCC CHECKDB ('MyDatabase') WITH ALL_ERRORMSGS, NO_INFOMSGS
Kristen

Unfortunately there is no backup available. It seems I can drop the DB and set up everything again

You don't see any chance in repairing the DB?

http://www.linuxhelpforum.de

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 11:12:11

"You don't see any chance in repairing the DB?"

If Paul Randall comes along shortly he'll give you a better answer to that one. Fingers crossed!

You could try creating a new DB and exporting to it piecemeal:


SET ROWCOUNT 1000
SELECT *
INTO NewDatabase.dbo.MyTable
FROM OldDatabase.dbo.MyTable AS T1
     LEFT OUTER JOIN NewDatabase.dbo.MyTable AS T2
         ON T2.MyPK = T1.MyPK
SET ROWCOUNT 0

Repeat this until you get an error, then reduce the ROWCOUNT at the start by at least half and try again - repeat that until ROWCOUNT = 1, then you've rescued all you can.

The try with a WHERE T1.MyPK > '... some value ...' to try to rescue more of the file from further up the index. If the PK index is not damaged you might be able to get the whole table out this way, likewise for any tables which are not corrupted.

You might also want to DROP all NON-PK indexes - in case the damage is restricted to them.

Perhaps you could also drop any PK that is NOT clustered - and then retry exporting that table - that will then have to use the raw data, rather than the PK index (it would maybe do that anyway, and its unlikely that you will have a PK that is NOT clustered ... but!)

"Unfortunately there is no backup available"

No sympathy for that here I'm afraid!

At the very least use the Maintenance Wizard to make a plan to backup "All user databases" and another one for "All system databases".

Right now would be a good time to do that - before something else breaks!

Run DBCC CHECKDB on all other databases on that box (including master etc.) to check that the damage is restricted to just the one database.

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-11 : 20:19:18

The errors can't be repaired because that would mean deleting rows from the sysindexes table. Try dropping and recreating statistics on various indexes. You can identify which indexes on which objects from the id and indid output in the DBCC error messages. id is the object and indid is the index.

And also get yourself a backup strategy.

Let me know how you get on.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

schoeppchen
Starting Member

3 Posts

Posted - 2006-05-12 : 03:54:44

You are both perfectly right - having no backup is a mess. But there's no use crying over spilt milk.

So I will do the fowllowing: I will drop the old database and resetup WSUS with a clean new database. Fixing the DB without knowing if it will work will take more time, so I will just start from the beginning and also implement a backup strategy for the DB - I'll promise :)

Thanks for your help!

http://www.linuxhelpforum.de

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources