Table sysindexes corrupt.

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.
rikkiAsked:
Who is Participating?
 
nigelrivettConnect With a Mentor Commented:
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
 
mgrajkumarCommented:
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
 
rikkiAuthor Commented:
I can't drop a system table.  The corrupted table is sysindexes.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
rikkiAuthor Commented:
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
 
ZhongYuCommented:
Hi rikki,

I had similar problem in master. The only solution was re-run setup.  
0
 
rikkiAuthor Commented:
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
 
ZhongYuCommented:
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
 
Jeremy_DCommented:
> 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
 
rikkiAuthor Commented:
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
 
Jeremy_DCommented:
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
 
rikkiAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.