473,699 Members | 2,892 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 2365
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
5683
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
6529
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
6830
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
8998
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
5409
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
3173
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
3837
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
5034
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
3376
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
8704
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
8623
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,...
0
9054
jinu1996
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...
1
8939
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8895
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
7778
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
5879
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
4390
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...
2
2362
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.