How do I drop and recreate system indexes

[John Eveleigh]

I have come across a user that has error 2525 errors on sysobjects tables throughout their SQL 6.5 system. The ids match and I can identify the index. But as they are system tables I cannot drop or rebuild the indexes.

Any ideas please?

The errors have been occuring for some time, so it is not an option to restore. I would like to avoid having to transfer data to and from another SQL Server.

Many Thanks

[Chuck Lucking]

On 7/17/98 8:06:56 AM, Bob wrote:
> You can drop and recreate system indexes, but first you must configure the
> server to allow updates (sp_configure or Enterprise Manager) Allow Updates
> should be set to 1(Don`t forget to set it back). Books on-line has info on
> error 2525.

If you try this approach you will get this type of message .....
Cannot drop the index `XXX` because it is a system index.
I would suggest the following...
If the problem is in master, try rebuilding the master database, or restore from a recent backup
If the problem is in a user database, you
1) could bcp out the data from all tables
2) Restore from recent backup
3) Truncate tables and bcp data back in
or
if you don`t have truncate on checkpoint set in the database and are backing up you transactions dumps you could
1) Make sure you dump the tran log (and save)
2) Restore from a recent backup (before the index went bad)
3) Reapply transaction dumps since the restored database

[Doug Snyder]

John,

I had a similar problem. I was able to interact with Microsoft abit. Basically here is what I needed to do. I ran dbcc checktable on both sysindexes and sysobjects to make sure there were no other problems on those tables. They came back clean, so there is a procedure called:

USE master
go
sp_dboption <db_name>,single,true
go

USE <db_name>
go

CHECKPOINT
go

sp_fixindex <db_name>,<system_table_name>,1
go

I hope this helps.

Thanks
Doug Snyder


On 7/15/98 9:15:03 AM, John Eveleigh wrote:
> I have come across a user that has error 2525 errors on sysobjects tables
> throughout their SQL 6.5 system. The ids match and I can identify the
> index. But as they are system tables I cannot drop or rebuild the
> indexes.

Any ideas please?

The errors have been occuring for some
> time, so it is not an option to restore. I would like to avoid having to
> transfer data to and from another SQL Server.

Many Thanks

[John Eveleigh]

Thanks for your replies. I have had to rebuild and transfer the data between servers though.

On 7/15/98 9:15:03 AM, John Eveleigh wrote:
> I have come across a user that has error 2525 errors on sysobjects tables
> throughout their SQL 6.5 system. The ids match and I can identify the
> index. But as they are system tables I cannot drop or rebuild the
> indexes.

Any ideas please?

The errors have been occuring for some
> time, so it is not an option to restore. I would like to avoid having to
> transfer data to and from another SQL Server.

Many Thanks

[Lu]

I ran a NT performance monitoring included serveral SQL specific counters.
I don&#39;t understand some of the results. Buffer cache hit ratio in SQL server Buffer Manager is close to 100%. But Cache hit ratio in SQL server Cache Manager is below 50%. Would you please help me in analyzing thoes data?
What is the real difference between those two counters.

Thanks a lot!

[Ray Miao]

Cache hit ratio is for data cache while buffer cache hit ratio is application specific. You need more memory since cache hit ratio is below 50%.


------------
Lu at 2/20/2002 10:05:50 AM

I ran a NT performance monitoring included serveral SQL specific counters.
I don&#39;t understand some of the results. Buffer cache hit ratio in SQL server Buffer Manager is close to 100%. But Cache hit ratio in SQL server Cache Manager is below 50%. Would you please help me in analyzing thoes data?
What is the real difference between those two counters.

Thanks a lot!

[Lu]

Thanks a lot. If it is possible, can you give a little more detail about
that buffer cache hit ratio is application specific? or please kindly direct
me to some recommended literature regarding this issue.




------------
Ray Miao at 2/20/2002 12:34:38 PM

Cache hit ratio is for data cache while buffer cache hit ratio is application specific. You need more memory since cache hit ratio is below 50%.


------------
Lu at 2/20/2002 10:05:50 AM

I ran a NT performance monitoring included serveral SQL specific counters.
I don&#39;t understand some of the results. Buffer cache hit ratio in SQL server Buffer Manager is close to 100%. But Cache hit ratio in SQL server Cache Manager is below 50%. Would you please help me in analyzing thoes data?
What is the real difference between those two counters.

Thanks a lot!