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_35 6BF102'
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 4 2069
Laurence Breeze (i.********@bla hblah.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_35 6BF102'
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****@sommarsk og.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
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
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_syste st1'.
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.
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.********@bla hblah.ac.ukwrot e 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_syste st1'.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: R |
last post by:
Hello everybody.
I'm writing my own Content System in PHP5. I've written so far main
classes for handling DB connections, XML, XForms and Sessions.
But I've got problem with one thing - it's not even relative with
implementation - I'm looking for a smart solution
My present system works like this:
|
by: Barry Anderberg |
last post by:
I'm using the .NET Memory Profiler by Sci Tech and I wrote a little
test application to verify something odd I observed and it appears
that System.Drawing.Font fails to dispose of its FontFamily.
I run the following code:
// BEGIN
while( true )
{
System.Drawing.Graphics dc=lblClock.CreateGraphics();
|
by: JohnnySparkles |
last post by:
Hi everyone,
I'm currently writing an application which uses the XmlSerializer class to
serialize/deserialize objects to/from xml.
Now when deserializing an XmlDocument back into the object, I'm using the
System::Type::GetType(String* typeName) to create a Type* needed to construct
the XmlSerializer.
The typeName argument is taken from the XmlDocument and consists of the
|
by: Niall |
last post by:
I'm not quite sure which groups to post this to, so short of a massive
crosspost, I decided on these two.
I am running into troubles with resource usage of our app on Win2k and above
systems. In several places, I have seen it said that the WinNT model only
limits resources to available memory. However, I have seen errors caused by
running out of resources on a machine with over 1GB of free memory (running
Win2k server), as well as on my...
|
by: Xah Lee |
last post by:
REQUIREMENTS FOR A VISUALIZATION SOFTWARE SYSTEM FOR 2010
Xah Lee, 2007-03-16
In this essay, i give a list of requirements that i think is necessary
for a software system for creating scientific visualization for the
next decade (2007-2017).
(for a HTML version with images, please see
http://xahlee.org/3d/viz.html )
| |
by: =?Utf-8?B?UGlnZ3k=?= |
last post by:
Hi to all,
I am getting this System.OutOfMemoryException calling the
Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(<stream>,<Obj>) method.
The type of <streamis IO.MemoryStream
=====Exception:
System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException'
was
thrown.
|
by: Ross Culver |
last post by:
'System.Web.Administration.WebAdminRemotingManager' in Assembly
'App_Code.1hpl8upp
Everytime I click the ASP Configuration button with VS2K5 this ERROR-RIDDEN
file is created in the
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET
Files\asp.netwebadminfiles\9d4caaa4\c2ae7372 directory; regardless of
whether I'm in a new project or old.
Does anyone know if I'm going to have to uninstall and reinstall Visual
|
by: =?Utf-8?B?c2FtMDFt?= |
last post by:
I have a remoting application that was developed on a Windows XP SP2 machine
with VS2005 SP1. I finally got everything deployed using Wix 3.0, and it
works great. Problem is, when I install the msi on a W23 server SP2, I get
the following error:
System.Runtime.Remoting.RemotingException: Remoting configuration failed
with the exception 'System.Reflection.TargetInvocationException: Exception
has been thrown by the target of an invocation....
|
by: =?Utf-8?B?Y2FzaGRlc2ttYWM=?= |
last post by:
Hi,
I am passing a couple of objects through a web service and get the error
message below when I check the event log:
System.Runtime.Serialization.SerializationException: The type
System.Web.HttpException in Assembly System.Web, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a is not marked as
serializable.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |