repeated corruption

Our company create's software with 3-4 Gb databases underneath. When a client has a corrupt database, 80-90% of the time the corruption occurs in the same index on the same table. (By corruption I mean DBCC CheckDB shows consistency or allocation e
rrors) The index itself is for only a single field but the table is one of our larger ones (1 million records) and the data inside the table does get changed a lot. Our solution is to drop and recreate the index which works most of the time.
Does anyone have any ideas on why this one index is always corrupting?
Is this index on a separate drive array than most? If it gets updated a lot
then it is more likely to see corruption if it is going to occur. Usually
repeated corruption is due to faulty hardware. You most likely have a drive
or controller getting ready to go.
Andrew J. Kelly SQL MVP
"Rod Harten" <Rod Harten@.discussions.microsoft.com> wrote in message
news:62407700-DE8F-4A5B-BC66-F69AA884F580@.microsoft.com...
> Our company create's software with 3-4 Gb databases underneath. When
a client has a corrupt database, 80-90% of the time the corruption occurs in
the same index on the same table. (By corruption I mean DBCC CheckDB shows
consistency or allocation errors) The index itself is for only a single
field but the table is one of our larger ones (1 million records) and the
data inside the table does get changed a lot. Our solution is to drop and
recreate the index which works most of the time.
> Does anyone have any ideas on why this one index is always
corrupting?
|||We set up the data server so the entire database runs on the same drive.
The table holding the index has more changes than any other table in our database - say 50% of the activity. However, the index itself is for a single int field that doesn't get updated very often after the data is added.
Another point is that copies of our software and the database are installed at our client sites. (Our clients are radio and TV stations.) Each site has its own hardware and database. At a guess, we have had 20 different sites (and different sets of har
dware) get corruption in this index. The problem is not recurring - once we drop and recreate the index at a site the problem goes away. I think we have had two sites that had the index go corrupt more than once in the span of 2 years.
Because the index is getting corrupt on different hardware, I assume it is not a hardware problem in most instances. Also, the fact that the problem goes away once it is fixed implies that it is not a hardware issue.
The only thing distinct about the index itself (and I admit this is grasping at straws) is that it's name appears alphabetically first in the list of 11 indexes on that table.
Thank you for you efforts!
Rod Harten
"Andrew J. Kelly" wrote:

> Is this index on a separate drive array than most? If it gets updated a lot
> then it is more likely to see corruption if it is going to occur. Usually
> repeated corruption is due to faulty hardware. You most likely have a drive
> or controller getting ready to go.
> --
> Andrew J. Kelly SQL MVP
>
> "Rod Harten" <Rod Harten@.discussions.microsoft.com> wrote in message
> news:62407700-DE8F-4A5B-BC66-F69AA884F580@.microsoft.com...
> a client has a corrupt database, 80-90% of the time the corruption occurs in
> the same index on the same table. (By corruption I mean DBCC CheckDB shows
> consistency or allocation errors) The index itself is for only a single
> field but the table is one of our larger ones (1 million records) and the
> data inside the table does get changed a lot. Our solution is to drop and
> recreate the index which works most of the time.
> corrupting?
>
>
|||From your original post and this comment:[vbcol=seagreen]
corrupting?
it sounded as if you had a single index that keeps getting corrupted. Is it
possible that each of the db's were created from a restored copy of the same
bases database? In that case the corruption could have been there in the
first place. Other than that I have not heard of a situation where an index
gets corrupted due to it's name type etc. Sorry not sure what else to say as
these days corruption is almost always due to some sort of hardware failure,
power glitch etc.
Andrew J. Kelly SQL MVP
"Rod Harten" <RodHarten@.discussions.microsoft.com> wrote in message
news:51AF73C7-F271-4A36-AEF2-DB7333081FD6@.microsoft.com...
> We set up the data server so the entire database runs on the same drive.
> The table holding the index has more changes than any other table in our
database - say 50% of the activity. However, the index itself is for a
single int field that doesn't get updated very often after the data is
added.
> Another point is that copies of our software and the database are
installed at our client sites. (Our clients are radio and TV stations.)
Each site has its own hardware and database. At a guess, we have had 20
different sites (and different sets of hardware) get corruption in this
index. The problem is not recurring - once we drop and recreate the index
at a site the problem goes away. I think we have had two sites that had the
index go corrupt more than once in the span of 2 years.
> Because the index is getting corrupt on different hardware, I assume it is
not a hardware problem in most instances. Also, the fact that the problem
goes away once it is fixed implies that it is not a hardware issue.
> The only thing distinct about the index itself (and I admit this is
grasping at straws) is that it's name appears alphabetically first in the
list of 11 indexes on that table.[vbcol=seagreen]
> Thank you for you efforts!
> Rod Harten
> "Andrew J. Kelly" wrote:
lot[vbcol=seagreen]
Usually[vbcol=seagreen]
drive[vbcol=seagreen]
When[vbcol=seagreen]
occurs in[vbcol=seagreen]
shows[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]