What could cause one index corrupted

[John]

I have one index on one table corrupted twice already (SQL 6.5). Once the index was rebuit the problem would be solved.

The potential problem is that it will happen again. I wonder if anyone of you knows what are the common causes to the problem where I should start with.

I appreciate it

[Brett Montgomery]

John,
Is the table updated by a view?

[John]

------------
Brett Montgomery at 8/3/99 6:32:15 AM

John,
Is the table updated by a view?

Brett:

Thanks.

I did not update it by a view. Please make any suggestions

[Brett Montgomery]

I know that several people have talked about frequent index problems with an insert from a view, I thought maybe you were having the same problem. Can you give some more detail on the table? How often does the index corrupt? Is this a large table? Are there a large amount of Inserts, deletes? Is the table static? How are the rows inserted, Access, VB, BCP? Is this a clustered index or non-clustered? How often do you rebuild the indexes and update the statistics?

------------
Brett Montgomery at 8/3/99 10:58:33 AM

I know that several people have talked about frequent index problems with an insert from a view, I thought maybe you were having the same problem. Can you give some more detail on the table? How often does the index corrupt? Is this a large table? Are there a large amount of Inserts, deletes? Is the table static? How are the rows inserted, Access, VB, BCP? Is this a clustered index or non-clustered? How often do you rebuild the indexes and update the statistics?

Answers to your questions:
1. Insert does not through a view
2. The index were corrupted only twice. about 1.5 month apart
3. The table size is 276000KB
4. The table is dynamic
5. The rows are inserted throght NT via ODBC
6. Clustered index only on four columns
7. I rebuild the index once it is corrupted. and I have not update the atatistics.

[Brett Montgomery]

John,
I think that I would make the following suggestions based on your responses,

Suggestions:
2. The index were corrupted only twice. about 1.5 month apart
This is a pretty respectable time difference for the corrupt index to occur. I would not worry about the table or problems unless it occured in back to back days. I know that we do not want any corruption to occur, but this far apart would be very hard to try and pinpoint.
4. The table is dynamic
With any table that is not static, you will always have a chance to corrupt an index.
7. I rebuild the index once it is corrupted. and I have not update the statistics.
The best way to make sure that indexes do not become corrupted is to do a little preventative maintenance. I rebuild the indexes on my servers once a week and update the statistics nightly. Check out the dbcc dbreindex (tablename). This is a good way to rebuild them before they become corrupt and have to be rebuilt. Also, if you do a lot of reads and inserts to a table, run the update statistics command to update the statistics often. This will help your response times by keeping the information in your index pages current. The information about these statements are in the Books on Line. One last thing, DBCC Reindex can take some time depending on table sizes and rows. Run the DBCC reindex when no one else is on the system and do a few tables at a time until you know exactly how long it will take.

Good Luck and I hope this helps you.

[Craig]

You may also want to ensure that you are on the latest fix packs for Nt and SQL Server. There were known corruption problems with DBCC DBREINDEX that have been fixed with recent fixpacks.

Good luck......


------------
Brett Montgomery at 8/3/99 2:45:45 PM

John,
I think that I would make the following suggestions based on your responses,

Suggestions:
2. The index were corrupted only twice. about 1.5 month apart
This is a pretty respectable time difference for the corrupt index to occur. I would not worry about the table or problems unless it occured in back to back days. I know that we do not want any corruption to occur, but this far apart would be very hard to try and pinpoint.
4. The table is dynamic
With any table that is not static, you will always have a chance to corrupt an index.
7. I rebuild the index once it is corrupted. and I have not update the statistics.
The best way to make sure that indexes do not become corrupted is to do a little preventative maintenance. I rebuild the indexes on my servers once a week and update the statistics nightly. Check out the dbcc dbreindex (tablename). This is a good way to rebuild them before they become corrupt and have to be rebuilt. Also, if you do a lot of reads and inserts to a table, run the update statistics command to update the statistics often. This will help your response times by keeping the information in your index pages current. The information about these statements are in the Books on Line. One last thing, DBCC Reindex can take some time depending on table sizes and rows. Run the DBCC reindex when no one else is on the system and do a few tables at a time until you know exactly how long it will take.

Good Luck and I hope this helps you.

------------
Craig at 8/4/99 8:37:28 AM

You may also want to ensure that you are on the latest fix packs for Nt and SQL Server. There were known corruption problems with DBCC DBREINDEX that have been fixed with recent fixpacks.

Good luck......


------------
Brett Montgomery at 8/3/99 2:45:45 PM

John,
I think that I would make the following suggestions based on your responses,

Suggestions:
2. The index were corrupted only twice. about 1.5 month apart
This is a pretty respectable time difference for the corrupt index to occur. I would not worry about the table or problems unless it occured in back to back days. I know that we do not want any corruption to occur, but this far apart would be very hard to try and pinpoint.
4. The table is dynamic
With any table that is not static, you will always have a chance to corrupt an index.
7. I rebuild the index once it is corrupted. and I have not update the statistics.
The best way to make sure that indexes do not become corrupted is to do a little preventative maintenance. I rebuild the indexes on my servers once a week and update the statistics nightly. Check out the dbcc dbreindex (tablename). This is a good way to rebuild them before they become corrupt and have to be rebuilt. Also, if you do a lot of reads and inserts to a table, run the update statistics command to update the statistics often. This will help your response times by keeping the information in your index pages current. The information about these statements are in the Books on Line. One last thing, DBCC Reindex can take some time depending on table sizes and rows. Run the DBCC reindex when no one else is on the system and do a few tables at a time until you know exactly how long it will take.

Good Luck and I hope this helps you.
-----------------------------------------------------------

Thank you, Brett and Craig, for your suggestions
John