Errors on SYSINDEXES

Question

Errors on SYSINDEXES

Viewing 12 posts - 1 through 13 (of 13 total)

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

apinho Mr or Mrs. 500 Points: 517 More actions · Answer 1

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...

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

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

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

apinho Mr or Mrs. 500 Points: 517 More actions · Answer 4

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 ?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

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

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

apinho Mr or Mrs. 500 Points: 517 More actions · Answer 6

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 ?

apinho Mr or Mrs. 500 Points: 517 More actions · Answer 7

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'.

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

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

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

apinho Mr or Mrs. 500 Points: 517 More actions · Answer 9

Ok Gail,

I'm gonna give a try...

Am I supposed to decrypt the SP :

- Before scripting them

- After scripting them

?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

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

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

apinho Mr or Mrs. 500 Points: 517 More actions · Answer 11

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...

Chris W SSChasing Mays Points: 625 More actions · Answer 12

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.