Solved

Table sysindexes corrupt.

Posted on 2001-06-14
11
900 Views
Last Modified: 2012-05-04
My sysindexes table is corrupt and causing a lot of problems.   It all started when I first changed the server setting to allows updates to system tables, created an index on the field 'name', then un-set the server setting to allow updates to system tables.  Now when I run DBCC checkdb or DBCC checktable, it shows that sysindexes is corrupt and there are entries in the sysindexes.xName that are not there and some that are extra.   Is this because I have allows updates to system tables turned off and the index is not getting written to?  

Now, when I try to remove the index I created, I get a message that I can't delete an index on a system table.  

If I run DBCC checktable with repair_rebuild, do I need to allow updates to system tables first?

Any input to this problem is greatly appreciated.  The problems occur when we use the IF EXISTS clause looking for indexes and create them or drop them as necessary, the condition gives us a false result.
0
Comment
Question by:rikki
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 1

Expert Comment

by:mgrajkumar
ID: 6192651
i do have the same problem but simply what i have done is drop the existing table and recreate  the new one and transfer  the datas to the New table
0
 

Author Comment

by:rikki
ID: 6192680
I can't drop a system table.  The corrupted table is sysindexes.
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
ID: 6192692
Creating an index on a system table?
This sounds like a good thing to try.

I would junk this database altogether.
Create a new one and try to dts all the tables across. Get the other objects by scripting.

If that fails then try creating all the objects by scripting then either bcp the data out and in or try inserts from one database to the other.
0
 

Author Comment

by:rikki
ID: 6192724
This sounds very drastic.  I am going to run the DBCC checktable with repair_build option first to see if that corrects it.  In the meantime, can someone answer my questions on the Server Setting - Allow updates to system tables.  Does it have to be one for the repair to work?
0
 
LVL 2

Expert Comment

by:ZhongYu
ID: 6194122
Hi rikki,

I had similar problem in master. The only solution was re-run setup.  
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:rikki
ID: 6194919
So, if I detach all my user databases, re-run setup and then attach the databases I will be ok?  Is this the cleanest way to do it?
0
 
LVL 2

Expert Comment

by:ZhongYu
ID: 6194937
Yes, if you fail to fix the probelm without setup.

You may not be able to dettach the databasees. But to be on the safe side, stop server, copy all the database files to a safe place before you proceed with setup.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6195281
> This sounds very drastic.

Drastic scr*w-ups ask for drastic actions. Whatever you choose to do, make sure you learn your valuable lesson from this (and I guess I don't have to tell what the lesson is anymore...)

In regard to your DBCC question: No, you don't have to allow updates to systables to allow DBCC to do its job.
0
 

Author Comment

by:rikki
ID: 6195304
You guys are really harsh, is there really a need for the attitudes?  And for future reference - the problem was fixed simply by executing DBCC checkdb ('database', REPAIR_REBUILD).

Maybe we should try humbling ourselves...
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6195452
Well, you could have read in about a zillion different places that you should _never_ mess with the system tables. If that wasn't clear to you yet a hars attitude might be wat is needed to get the point through.

Don't feel offended by me though, any harsh comments from my person you can take with a 'tongue in cheek' attitude (yes, I know, I should have used a smiley somewhere :)
0
 

Author Comment

by:rikki
ID: 6195485
I appreciate everyone's help.  Jeremy - don't worry I did not get offended.  I only hope that those who I look for answers from and are considered experts would be more professional.  It's not a perfect world!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now