duplicate index key problem

[JohnMcM]

sql 7 adds additional indexes automatically with useage. the names of these indexes can be seen in sysindexes.name and start with "_WS_Sys_". over time, more and more are created. eventually the application comes up with "duplicate index key". my only solution at the moment is to export the data, re-create the tables and then import again. this creates just the explictly defined indexes and my application is again happy for a while.

dbcc dbreindex doesnt help

so here's what i need to understand.

1. Are "_WS_Sys_" indexes a normal feature
2. Do "_WS_Sys_" indexes need to be managed and/or culled from time to time
3. How can i remove these temporary indexes?

i've really tried to understand this one (bol etc) but i need some inspiration!

thanks in advance

JohnMcM

 

[fluteplr]

They are not actually indexes they are system statistics.
The duplicate key thing baffles me though anyone else have an idea.

A thought.

Are you sure the duplicate index key has anything to do with this error? Usually a message like that indicates a duplicate entry into a column on a table with a unique index or constraint. Not that there are multiple indexes with the same key.

 

[JohnMcM]

the duplicate index key message is from my application (delphi).
i dont really know whether the problem is application related or not. i just know that import/export, which clears the _WA_Sys system statistics resolves the problem till the next time.

it currently happens once every couple of weeks. the system has 50+ (very active) users, and around 600mb of data.

the table that tends to suffer the problem ends up with over 100 "_WA_Sys_.." entries