May 11, 2009 at 4:34 am
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...
May 11, 2009 at 4:37 am
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...
May 11, 2009 at 7:02 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2009 at 7:15 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2009 at 7:18 am
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 ?
May 11, 2009 at 7:22 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2009 at 7:32 am
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 ?
May 11, 2009 at 7:40 am
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'.
May 11, 2009 at 7:44 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2009 at 7:56 am
Ok Gail,
I'm gonna give a try...
Am I supposed to decrypt the SP :
- Before scripting them
- After scripting them
?
May 11, 2009 at 7:57 am
Well, you can't script an encrypted stored proc so.....
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 11, 2009 at 8:53 am
Follow-up (For those who might need to walk the same path ):
Scripting the SQL2000 database, through SQL Entreprise Manager :
Right click on MYDBASE, choose "All tasks", then "Generate SQL script"
On the "generate SQL script" window :
Enable "Show all" then enable "Script all objects"
Switch to "Formatting"
Enable all options but not the last one : "Only script 7.0 compat features"
Switch to "Options"
Enable all options. Keep "International text (Unicode)"
and keep "Create one file"
Choose OK
Select name for the SQL file.
This takes about 5 mns (Depending on the DB complexity, of course)...
Next step : bulkcopying the DB...
October 27, 2009 at 9:35 am
I appear to have the same problem. DBCC CHECKDB identifies 2 pages with errors. From there DBCC PAGE identifies objID=2 and indexID=255 (tsysindexes) index.
I would be very interested to learn if the suggested solution worked (creating a new database and bcp'ing the data out of the old database into the new database)? Were there any gotchas to watch for?
After creating a copy of the source database and loading data into it what's the best approach to take to "replace" the old database with the new one? Peform database renames or some form of detach/attach or perhaps another approach?
Thanks in advance!
Chris.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply