473,722 Members | 2,264 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Index corruption on table without key: what could be the reasons ?

Dear group,

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don't have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

How can i prevent this from happening again, as long as there is no
updated database / application ?

I'd be grateful for any useful comment

Regards

Uli

Sep 14 '05 #1
14 2367
Stu
Just to clarify a common misconception: key constraints are used to
enfore data integrity, and indexes are used to enhance performance.
While primary keys also introduce an index upon creation, they are not
the same thing; you can create indexes without having primary keys at
all (this is a technique that can be used in data warehouses, where the
normalization rules are a bit "looser"). Index fragmentation is
unrelated to the presence or absence of keys; it can be, however,
related to the underlying clustered index that is (by default)
associated with the primary key.

A couple of questions: do you have a clustered index on this table?
On what column is it located?

Do you know how to use the DBCC command SHOWCONTIG? It will help you
identify it is fragmentation, and where it is occurring.

http://www.sql-server-performance.co...showcontig.asp

HTH,
Stu

Sep 14 '05 #2
Hi

You do not say what the index is on, and whether it is a unique index.

You may want to read:
http://www.aspfaq.com/show.asp?id=2081
http://www.aspfaq.com/etiquette.asp?id=5006

John


<ul*********@ly cos.at> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .
Dear group,

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don't have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

How can i prevent this from happening again, as long as there is no
updated database / application ?

I'd be grateful for any useful comment

Regards

Uli

Sep 14 '05 #3
(ul*********@ly cos.at) writes:
we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don't have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?


If the duplicates disappear after a DBCC DBREINDEX (or DROP + CREATE, then
it is the index that is corrupted.

I seem to recall that there is an issue with heap tables that could cause
this. (A heap table is a table that does not have a clustered index.)
Can you define the index as clustered? Even better if you can add UNIQUE to
enforce uniqueness. Then again, it sounds as if the application is able
to insert duplicates?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 14 '05 #4
dont ever make a single table in your whole life that doesn't have a
single-column identity primary key

Sep 28 '05 #5
Stu
Please tell me you're joking.

Stu

Sep 29 '05 #6
<ul*********@ly cos.at> wrote:
Dear group,

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don't have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

How can i prevent this from happening again, as long as there is no
updated database / application ?

I'd be grateful for any useful comment

Regards

Uli

Uli,

Depending on how often your devices are firing inserts and how the timestamp
column is defined, I would expect to find multiple rows for a single
deviceno and timestamp combination (especially if you're using
smalldatetime).

But if I understand you correctly, you drop and re-create an index (or use
DBCC REINDEX) and the SELECT you posted returns different results (and this
happens consistently). If that's the case, you need to call MS support:
that's not supposed to happen.

Craig
Sep 29 '05 #7

<db*******@hotm ail.com> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.com...
dont ever make a single table in your whole life that doesn't have a
single-column identity primary key
This is amazingly poor advice.


Oct 2 '05 #8
why?

aren't all indexes slower if you have your clustered index on a
multi-column varchar field?

i mean.. INT IDENTITY EVERYWHERE

Oct 7 '05 #9
no greg im serious

why is it bad advice?

i just dont believe in tables that dont have a single numeric primary
key.

I'm just too friggin tired to use 'A345FD' as a primary key anymore..
use INTs or BIGINTs if you want. i would rather use a bigint than a
varchar any day of the week

but.. i mean.. no keys?

are you drunk?

Oct 11 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5686
by: Thiko | last post by:
Hi I have a corrupt PK index on a table column. It is a unique PK. It needs to be dropped and recreated to cure the corruption. The table is on a backup database which is in replication having records added to it to keep the two databases in sync. Am I correct in think that if I:
1
6531
by: J. C. Clay | last post by:
We are having some troubles with corrupt indexes on our SQL 2000 dbase. It is only affecting 2 tables out of about 150 we have. These 2 tables are heavily used. We have run dbcc repairs; dropped and recreated indexes but the problem persists
2
6837
by: David Schwartz | last post by:
We are having quite a time since moving a large database to a new server (actually built new server, renamed as old to make seamless for users, etc.) Import 104 million row database (5 column) into table (CD_Assets_bad2) from existing (CD_Assets): Account(varchar(8)) TransactionDate(datetime(8) Flow(varchar(1)) Category(varchar(7))
8
9011
by: Kragen Sitaker | last post by:
ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index We've been getting this error in our application every once in a while --- typically once an hour to once a day, although it varies over time. The daemon that gets the error exits and restarts a few seconds later. Usually it's fine then, but sometimes the error happens three or more times in a row. Occasionally, instead, we get "ERROR: Cannot insert a...
14
5412
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB 7.2 environment, the choices the optimizer makes often seem flaky. But this last example really floored me. I was hoping someone could explain why I get worse response time when the optimizer uses two indexes, than when it uses one. Some context:
9
3177
by: WalterR | last post by:
This is my first time here, so there may be earlier relevant threads of which I am unaware. Though my experience with DB2 is not extensive, such as it is was under OS/390 or equ. My main experience is IMS DB, which leads to my question. In IMS, there is an HDAM access method which can find a record without using an index as such. At initial database load, it first formats the entire space allocation into blocks of the given size. ...
3
3838
by: Bari Allen via AccessMonster.com | last post by:
I have a database in a Front-End/Back-End User Environment, which has been running fine, for 4 years. Lately, when the users try to enter data (from a form into a single table) they've been getting the message that they can't add the records, because it would create "duplicate Values in the Index". The table's Primary Key is an AutoNumber Increment field. To solve the problem, I copied the table structure to a new table, and appended...
18
5036
by: Dave | last post by:
Guys I am really stuck on this one. Any help or suggestions would be appreciated. We have a large table which seemed to just hit some kind of threshold. They query is somewhat responsive when there are NO indexes on the table. However, when we index email the query takes forever. FACTS - The problem is very "data specific". I can not recreate the
1
3379
by: brian.j.parker | last post by:
One of the developers where I work found that some of his code would regularly cause index corruption in SQL Server 2000, post-sp4. @@version is 8.00.2050 and I can repeat this, so I can't (re-)apply the hotfix for KB article 929440. I know the code is not a good way to do what he's doing, which I explained to him. But corrupting the index on the table probably isn't the right reaction for SQL Server. A script to recreate the error...
0
8860
marktang
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...
0
9382
Oralloy
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...
0
9087
tracyyun
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...
0
8043
agi2029
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...
0
5995
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();...
0
4502
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2601
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.