|
|
Posted by Craig Kelly on 09/29/05 07:41
<uli2003wien@lycos.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
Navigation:
[Reply to this message]
|