Solved

Table sysindexes corrupt.

Posted on 2001-06-14
11
912 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
Create a Calendar table 29 43
T-SQL: Trying to use a "NOT IN (Subquery)" in CASE Statement 2 30
Creating Scalar Function 3 17
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 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