REINDEX - Why?

[francoislaroche]

I'd like to know what can happens that causes an INDEX to become corrupted, thus forcing you to use the REINDEX command so that everything function properly again?

 

[DSummZZZ]

A lot of possibilities, but the culprits are usually someone shutting down their computer improperly. Like turning it off or rebooting because of impatience, while they have files open. Other culprits are network traffic, bad NIC cards, bad server configuration or even hard drives that need to me defragged. Start with eliminating those problem areas.

Dave S.

 

[francoislaroche]

What can we do to protect data from such things?

 

[DSummZZZ]

If it's a user that is causing the corruption, the best thing to do is make sure the files are being updated as quickly as possible. In other words, don't perform an APPEND BLANK, then while having the record added and locked make all the edits etc. Make all the edits to memory variables, then add them using INSERT INTO or APPEND BLANK and GATHER MEMVAR. Using the record buffering features is a lot more reliable too. For the network issues, you'll have to verify where any bottlenecks are, or determine where the bad network cards may be and replace them.

Dave S.

 

[rgbean]

For more info on possibilities, go to

http://www.craigberntson.com/articles.htm

and read "Avoiding Table and Index Corruption".

Rick

 

[francoislaroche]

Thanx for the link, it was quite instructive.

DSUmm: I never use the APPEND BLANK. Only the INSERT INTO command.

Anyway, from what I see, I guess I'll have to mostly check the server.

 

[ramani]

HI francoislaroche

Before any Table update, do transaction processing

BEGIN TRANSACTION
&& DO MY TABLE UPDATE
&& never allow any sort of user input .. in here
END TRANSACTION

Doing a FLUSH also helps in clearing memory buffers and power failures. I almost always use transaction processing and hardly get into any corruption.. may be once in a year.. I dont remember.
ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[francoislaroche]

By table update, do you imply only the modification of the structure of the table (like adding a field or an index), or even the simple modifications of the data contained in the tables?

 

[chpicker]

He's talking about any changes to the data contained in the tables. Transactions aren't for modifying table structure.

Ian

 

[fluteplr]

One other note. Unless you are in an area where the electric power is absolutely stable. Put a UPS on all workstations, the server and any hubs, switches etc. that are on the network.

A UPS good for one workstation is around 100$, cheap insurance compared to the amount of time it takes to find and fix data corruption.

 

[francoislaroche]

Well, I don't think we can afford UPS for every workstation, but the server got one. The problem is that my application is used by another institution, and they don't have anybody on site, so I can't know if their server crashed during the last week or so (I think that the problem causing the corruption happened during this laps of time).

Anyway... could it be a good idea to make a program running during the night on the server that would just REINDEX every table every day (assuming I can find a way to open the tables in exclusive mode)?