Solved

Table sysindexes corrupt.

Posted on 2001-06-14
11
908 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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
 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to share SSIS Package? 6 37
Help creating a spatial object in SQL Server 4 22
Need help how to find where my error is in UFD 6 28
Sql Server group by 10 27
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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