Posted by Erland Sommarskog on 09/14/05 16:06

(uli2003wien@lycos.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, esquel@sommarskog.se

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

[Back to original message]