Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Errors on SYSINDEXES Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:04 AM
Points: 7, Visits: 14
Hello everyone,

I have this customer wich has a problem with a SQL2000 DB.

He has daily backups, on a weekly rotation, but I've tested all his backups and they all have the same errors. Apparently, the error hasn't bothered them so far, so they just didn't notice it. He does have much older backups, but the DB DDL has changed a lot since...

Errors on DBCC CHECKTABLE('mydbase','sysindexes') WITH NO_INFOMSGS, ALL_ERRORMSGS :
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 33816576 owned by data record identified by RID = (1:24:3) id = 2 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175064576 owned by data record identified by RID = (1:526:13) id = 238623893 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175130112 owned by data record identified by RID = (1:526:14) id = 238623893 and indid = 2.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175195648 owned by data record identified by RID = (1:526:15) id = 238623893 and indid = 3.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175261184 owned by data record identified by RID = (1:526:16) id = 238623893 and indid = 4.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175326720 owned by data record identified by RID = (1:526:17) id = 238623893 and indid = 5.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175392256 owned by data record identified by RID = (1:526:18) id = 238623893 and indid = 6.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175457792 owned by data record identified by RID = (1:526:19) id = 238623893 and indid = 7.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175523328 owned by data record identified by RID = (1:526:20) id = 238623893 and indid = 8.
Server: Msg 8928, Level 16, State 1, Line 4 Object ID 2, index ID 255: Page (1:17) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 4 Table error: Object ID 2, index ID 255, page (1:17). Test (m_freeCnt == freeCnt) failed. Values are 1116 and 758.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 1, text ID 153175064576 is referenced by page (1:526), slot 13, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 2, text ID 33816576 is referenced by page (1:24), slot 3, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 4, text ID 153175130112 is referenced by page (1:526), slot 14, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 6, text ID 153175195648 is referenced by page (1:526), slot 15, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 8, text ID 153175261184 is referenced by page (1:526), slot 16, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 10, text ID 153175326720 is referenced by page (1:526), slot 17, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 12, text ID 153175392256 is referenced by page (1:526), slot 18, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 14, text ID 153175457792 is referenced by page (1:526), slot 19, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:17), slot 15, text ID 153175523328 is referenced by page (1:526), slot 20, but was not seen in the scan.
Server: Msg 8964, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:5033), slot 0, text ID 33816576 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:33889), slot 0, text ID 153175195648 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 4 Table error: Object ID 2. The text, ntext, or image node at page (1:33890), slot 0, text ID 153175523328 is not referenced.
DBCC results for 'sysindexes'.

Problem is :
- All daily backups are alike, so restoring them is pointless.
- I can't reindex SYSINDEXES.
- Running DBCC repairs just doesn't work.

Questions :
1 - Can I just drop index ID255 (tsysindexes) from object ID2 (sysindexes), and then recreate with ? How do I do that ?
2 - Just what is this TSYSINDEXES about, anyway ? Can't seem to find any valuable information about in on the Net...


EDIT : Oups, sorry ! I've just realized I posted at the wrong place. Could some admin, please, put my post on the "data corruption" box ?
Thak you...
Post #713965
Posted Monday, May 11, 2009 4:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:04 AM
Points: 7, Visits: 14
Oups, sorry ! I've just realized I posted at the wrong place. Could some admin, please, put my post on the "data corruption" box ?
Thak you...
Post #713966
Posted Monday, May 11, 2009 7:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
Please run the following and post the full results.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS

Take a look at this article. http://www.sqlservercentral.com/articles/65804/, specifically the section on 'irreparable corruption'

How critical is the data in this database?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #714062
Posted Monday, May 11, 2009 7:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
Index 255 is the Large Object data for a table. For sysindexes, it's going to be the data in the column statsblob, as that's the only LOB column in the table. Fortunately, that's not critical, it's the statistics info for the indexes in question. Hence you're unlikely, from what I can see, to lose any data because of this (unless there are other corruptions). However I don't believe this is repairable. CheckDB will not fix the system tables and trying to do anything manually is likely to cause worse problems, seeing as it appears that some of the indexes on the system tables are damaged.

Suggestion:
Create new database, script all tables and objects and create them in the new DB. bcp all data out and reload into the new database.

Perhaps point out to the client that ignoring corruptions is not a clever thing to do, regardless of whether or not it seems to be causing problems. Had this been picked up shortly after it happened, a backup could have been restored and the log backups rolled forwards.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #714079
Posted Monday, May 11, 2009 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:04 AM
Points: 7, Visits: 14
Hi Gail, and thank you,

I'm sure going to check on you link...

About the DBCC you're asking me for :
The series of errors on my first post is indeed the result of a :
DBCC CHECKTABLE (..) WITH NO_INFOMSGS, ALL_ERRORMSGS.
Running a DBCC CHECKDB instead, shows the exactly same errors, since there are no other errors on this DB. Do you still want me to produce it ?
Post #714084
Posted Monday, May 11, 2009 7:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
I just want to be 100% sure that there are no other errors anywhere else in that database. If the results of checkDB are identical to the results of CheckTable on sysindexes, don't post.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #714089
Posted Monday, May 11, 2009 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:04 AM
Points: 7, Visits: 14
Forgot to say a few things :

Yes, Gail, data is critical.

Yes, we've been over it with the customer telling him to pay more attention the next time.

I also think the solution you just gave me is the only one : Rebuilding it all from scratch...
Problem is, I'm too much of a newbie to perform that. For instance : I don't really understand when you tell me to "script things".
So, bottomline, I was trying for some "miracle solution" to get me through, but this solution, of course, doesn't exist at all...

Do you know some link where I can see that "script objects / bulkcopy data" trick, thoroughly explained, so I don't make things worse.
A colleague of mine (Better DBA than me, but no longer here) already tried something of the kind, with a backup DB, but things didn't work out because (quoting) "some SP were encrypted". Rings a bell ?
Post #714097
Posted Monday, May 11, 2009 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:04 AM
Points: 7, Visits: 14
Yep, just reran check with ALL_ERRORMSGS, and output is the almost the same: Only difference, of course, is the additional line at the end :
CHECKDB found 0 allocation errors and 23 consistency errors in database 'MYDBASE'.
Post #714107
Posted Monday, May 11, 2009 7:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
apinho (5/11/2009)

Do you know some link where I can see that "script objects / bulkcopy data" trick, thoroughly explained, so I don't make things worse.
A colleague of mine (Better DBA than me, but no longer here) already tried something of the kind, with a backup DB, but things didn't work out because (quoting) "some SP were encrypted". Rings a bell ?


It's not that hard.

Generate scripts of all the tables. Use Enterprise manager, I know it has that functionality somewhere. Make sure that you script all the primary keys, indexes, foreign keys, defaults and constraints.
Generate scripts of all views. Also through Enterprise manager
Same with the procedures. If they're encrypted, google for a decryption tool for SQL 2000 stored procs. (NB, make sure it's for SQL 2000). The encryption's not hard to crack on SQL 2000.

Look up the details of bcp in books online. You need to bcp each table out to a file, then, once you've created a new database and recreated all of the tables, use bcp to reload all of the tables.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #714118
Posted Monday, May 11, 2009 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:04 AM
Points: 7, Visits: 14
Ok Gail,
I'm gonna give a try...

Am I supposed to decrypt the SP :
- Before scripting them
- After scripting them
?
Post #714129
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse