System objects problem

[Laurence Breeze]

I wonder if anyone can help ...

Today I tried to create another non-clustered index on a table. This
failed as I apparently already had 249 non-clustered indexex on the
table. Looking at the definition of the table there were 90 or so
indexes already defined and not 249. (For those of you who quite
rightly think 90 indexes on a table is a little over the top, I hasten
to add that this is a third party CRM system called "Siebel" which comes
with it's own database). However, examining the sysindexes system
catalog I find that there are indeed 249 entries, but that many
(possibly all) of them have names of the following type:

'_WA_Sys_XYZ_356BF102'

where 'XYZ' is a column on the table in question.

Looking at the same database across our DEV/TEST/PROD environments I
notice that these system like index entries are not consistant. The
names and number of entries differ. I've tried creating a new table
from a script generated in EM and no such indexes/system objects are
created.

I'm confused. What are these things. I can't drop them, sp_helpindex
doesn't refer to them. Examining the sysindexes/sysindexkeys catalogs
appears to suggect that the indexes have columns but no rows/entries.

I've got around the immediate problem by deleting one of these entries
from sysindexes and I've been able to create the index I wanted. All
seems well, but is this likely to cause a problem ?

Thanks in advance

Laurence Breeze

[Erland Sommarskog]

Laurence Breeze (i.********@blahblah.ac.uk) writes:

However, examining the sysindexes system
catalog I find that there are indeed 249 entries, but that many
(possibly all) of them have names of the following type:

'_WA_Sys_XYZ_356BF102'

where 'XYZ' is a column on the table in question.

Looking at the same database across our DEV/TEST/PROD environments I
notice that these system like index entries are not consistant. The
names and number of entries differ. I've tried creating a new table
from a script generated in EM and no such indexes/system objects are
created.

I'm confused. What are these things. I can't drop them, sp_helpindex
doesn't refer to them. Examining the sysindexes/sysindexkeys catalogs
appears to suggect that the indexes have columns but no rows/entries.

They are auto-generated statistics, and an sp_helpstats will list them.

I've got around the immediate problem by deleting one of these entries
from sysindexes and I've been able to create the index I wanted. All
seems well, but is this likely to cause a problem ?

Delete? Does that mean that you turned on Allow updates and operated
on sysindexes directly? That's always a risk, as you may cause some
inconsistency in the system catalog by not deleting all. For instance,
I would expect that you need to delete the entries in sysindexkeys as
well.

Next time you should use DROP STATISTICS.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Fri, 04 May 2007 15:25:31 +0100, Laurence Breeze wrote:

(snip)

>I've got around the immediate problem by deleting one of these entries
from sysindexes and I've been able to create the index I wanted. All
seems well, but is this likely to cause a problem ?

Hi Laurence,

Erland already wrote that this might cause inconsistency. I'll take it a
step further ans say that you probably HAVE caused inconsistency. One of
the things you'll probably have damaged is the admnistration of free and
allocated disk space. You might also have caused further damage.

I'd advice you to execute a DBCC CHECKDB immediately, and make sure that
you know where you stored the most recent backup of your database as it
might, if you're unlucky, even be damaged beyond repair.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Laurence Breeze]

Thanks to both of you for your advice. I've run DBCC CHECKDB on the
databse and there are no errors:

CHECKDB found 0 allocation errors and 0 consistency errors in database
'siebeldb_systest1'.

I notice I didn't say in my original message that I did this on a
non-live database.

I've also run DROP STATISTICS for each of the non-index entries in the
sysindexes catalog for the table in question successfuly. There are now
only 90 entries for this table - all of which are indexes.
This has got around the immediate problem. However, it seems to me that
there is an underlying problem in SQLServer (2000). It appears not to
allow the creation of an index when there are less than the maximum 249
indexes allowed on a table because there are entries in the sysindexes
table that are related to statistics - and nothing to do with indexes.

Perhaps I'm missing something - but this seems downright wrong. How do
others deal with this issue.

TIA

Laurence


Hugo Kornelis wrote:

On Fri, 04 May 2007 15:25:31 +0100, Laurence Breeze wrote:

(snip)

>>I've got around the immediate problem by deleting one of these entries

>>from sysindexes and I've been able to create the index I wanted. All

>>seems well, but is this likely to cause a problem ?

Hi Laurence,

Erland already wrote that this might cause inconsistency. I'll take it a
step further ans say that you probably HAVE caused inconsistency. One of
the things you'll probably have damaged is the admnistration of free and
allocated disk space. You might also have caused further damage.

I'd advice you to execute a DBCC CHECKDB immediately, and make sure that
you know where you stored the most recent backup of your database as it
might, if you're unlucky, even be damaged beyond repair.

[Dan Guzman]

This has got around the immediate problem. However, it seems to me that

there is an underlying problem in SQLServer (2000). It appears not to
allow the creation of an index when there are less than the maximum 249
indexes allowed on a table because there are entries in the sysindexes
table that are related to statistics - and nothing to do with indexes.

Perhaps I'm missing something - but this seems downright wrong. How do
others deal with this issue.

One workaround is the one you've already done - delete autocreated stats to
make room for a new index. I wouldn't go as far as to delete all stats to
make room for a new index, just the ones least likely to be used in WHERE
clauses. Another is to turn off auto create statistics and create stats
manually when desired.

This limitation is mitigated in SQL 2005, where you can have up to 249
non-clustered indexes plus 2000 statistics per table.
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Laurence Breeze" <i.********@blahblah.ac.ukwrote in message
news:46**************@blahblah.ac.uk...

Thanks to both of you for your advice. I've run DBCC CHECKDB on the
databse and there are no errors:

CHECKDB found 0 allocation errors and 0 consistency errors in database
'siebeldb_systest1'.

I notice I didn't say in my original message that I did this on a non-live
database.

I've also run DROP STATISTICS for each of the non-index entries in the
sysindexes catalog for the table in question successfuly. There are now
only 90 entries for this table - all of which are indexes.
This has got around the immediate problem. However, it seems to me that
there is an underlying problem in SQLServer (2000). It appears not to
allow the creation of an index when there are less than the maximum 249
indexes allowed on a table because there are entries in the sysindexes
table that are related to statistics - and nothing to do with indexes.

Perhaps I'm missing something - but this seems downright wrong. How do
others deal with this issue.

TIA

Laurence


Hugo Kornelis wrote:

>On Fri, 04 May 2007 15:25:31 +0100, Laurence Breeze wrote:

(snip)

>>>I've got around the immediate problem by deleting one of these entries

>>>from sysindexes and I've been able to create the index I wanted. All

>>>seems well, but is this likely to cause a problem ?

Hi Laurence,

Erland already wrote that this might cause inconsistency. I'll take it a
step further ans say that you probably HAVE caused inconsistency. One of
the things you'll probably have damaged is the admnistration of free and
allocated disk space. You might also have caused further damage.

I'd advice you to execute a DBCC CHECKDB immediately, and make sure that
you know where you stored the most recent backup of your database as it
might, if you're unlucky, even be damaged beyond repair.