Ghost Indexes: Index state unknown

Ghost Indexes: Index state unknown

Post by Bhala Ghatat » Mon, 05 Feb 2001 07:25:08



Hi all,
 this problem keeps occuring on sqlserver 2000 running on windows 2000
advanced server.
 We load these tables nightly and drop and recreate the indexes.
  Almost every time we are done with the load sqlserver doesn't know about
some of the indexes.
 It thinks the indexes are there but you can't drop them noe can you create
them.
 Every time we have to copy the tables to temp tables.  Recreate the table
and copy the data back.

Any help will be appreciated.
Below are te queries which will explain it.

This shows that only the de_00_pk index exists.
sp_helpindex temp_de_00
result:
de_00_pk nonclustered, unique, primary key located on DEINDXFILEGROUP
userid, domainid, machinedateint, logdateint

This shows that I can't create a new index called logdateint
create index logdateint on temp_de_00(logdateint)
Result:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'temp_de_00' named 'logdateint'.

This shows that I can't drop it either.
drop index temp_de.logdateint
Result:
Server: Msg 3703, Level 11, State 6, Line 1
Cannot drop the index 'temp_de.logdateint', because it does not exist in the
system catalog.

--
Bhala Ghatate [cel: 512 657 3020 jfax:(714)9089976]

 ***********************************************
 www.damanconsulting.com
Where Business Intelligence Clicks

--
Bhala Ghatate [cel: 512 657 3020 jfax:(714)9089976]

 ***********************************************
 www.damanconsulting.com
Where Business Intelligence Clicks

 
 
 

1. Ghost Indexes: Index state unknown

Hi all,
 this problem keeps occuring on sqlserver 2000 running on windows 2000
advanced server.
 We load these tables nightly and drop and recreate the indexes.
  Almost every time we are done with the load sqlserver doesn't know about
some of the indexes.
 It thinks the indexes are there but you can't drop them noe can you create
them.
 Every time we have to copy the tables to temp tables.  Recreate the table
and copy the data back.

Any help will be appreciated.
Below are te queries which will explain it.

This shows that only the de_00_pk index exists.
sp_helpindex temp_de_00
result:
de_00_pk nonclustered, unique, primary key located on DEINDXFILEGROUP
userid, domainid, machinedateint, logdateint

This shows that I can't create a new index called logdateint
create index logdateint on temp_de_00(logdateint)
Result:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'temp_de_00' named 'logdateint'.

This shows that I can't drop it either.
drop index temp_de.logdateint
Result:
Server: Msg 3703, Level 11, State 6, Line 1
Cannot drop the index 'temp_de.logdateint', because it does not exist in the
system catalog.

--
Bhala Ghatate [cel: 512 657 3020 jfax:(714)9089976]

 ***********************************************
 www.damanconsulting.com
Where Business Intelligence Clicks

2. Installation Mini Sap Basis

3. Ghost Indexes. Index state unknown

4. How to create unique primary index (autoinc feature)?

5. Ghost indexes. Index state unknown

6. upgrading NT server

7. Ghost indexes, how to delete them

8. New Data Type in Linux ASE 12.5?

9. INDEX( index, index, ... )

10. Getting Unknown Error When Performing a full text index query

11. Unknown Indexes In SYSINDEXES System table

12. Full-Text Indexing -Fails with unknown result?

13. Getting or creating indexes for an unknown dBASE or Paradox database at runtime