Solved

Table sysindexes corrupt.

Posted on 2001-06-14
11
903 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

17 Experts available now in Live!

Get 1:1 Help Now