|
|
Posted by Dan Guzman on 05/08/07 11:50
> 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.l.breeze@blahblah.ac.uk> wrote in message
news:46404928.5070505@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.
>>
>
Navigation:
[Reply to this message]
|