Subject Re: Automatically deleting bad duplicate records
Author Adam
> > Does the database have forced writes on?
> Forced writes was not on when corruption took place, we have since
> switched forced writes on.


You need not investigate any further. Having Forced writes switched off
means that when the database writes a page, the OS can cache the
changes. It is possible that there was a problem (my guess power loss)
between the record being written to the data page and the index page
being updated to reflect the new record. The constraint checks rely on
this index to detect whether a value exists or not. Forced writes
forces the changes to be persisted to disk immediately as they are
made. This means that the record would have been long on disk before
the power went off.

Some OSes are worse than others when it comes to forced writes,
increasing the likelihood of corruption.

So you do not need an explanation as to how it happenned. It happenned
because you (or the person who set them up) was running the database
with Forced writes off. It is a mode that can deliver higher
performance, because it doesn't have to wait for the I/O channel, but
it has many documented risks and it is only suggested that you run in
this mode if database corruption is not an important problem. In the
end, Murphy was right.

Hopefully a duplicate record was the only consequence. You will need to
locate the duplicate record(s) and remove at least one of them (or
renumber them appropriately). I would then backup and restore to make
sure none of the other structures were damaged.