Re: System objects problem

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.


.

Relevant Pages

  • Re: SELECT COUNT(*) returning wrong value
    ... > Thanks for replying Adam, ... > I ran the DBCC CHECKDB and it reported 0 allocation errors and 0 ... > I tried a variation of the original SELECT and it reports 6 ...
    (microsoft.public.sqlserver.server)
  • Re: SELECT COUNT(*) returning wrong value
    ... I ran the DBCC CHECKDB and it reported 0 allocation errors and 0 consistency ... I tried a variation of the original SELECT and it reports 6 ... >> am I missing? ...
    (microsoft.public.sqlserver.server)
Loading