We have some databases with index fragmentation that is > 95%. As best I can tell the indexes have never been rebuilt much less reorganized. In years.
(In fairness, these tables do seem to have auto-updated statistics enabled. Also in fairness, he is diligent about backups: full daily and trx logs hourly.)
When I asked, the DBA said he was reluctant to rebuild or reorg the indexes. When I asked why, he couldn't really articulate it. Eventually he said he was concerned about potential data loss. For instance one of the databases is used by our Great Plains Dynamics accounting application, and he seemed very anxious about that.
I am not a DBA but from what I've read, his anxiety seems … difficult for me to understand.
I am not sure what do to next. Suggestions how I should proceed?
Best Answer
Rebuilding a database index should not cause any data loss. It will however probably cause a substantial performance degradation as the indexes being rebuilt will normally not be available for use until the rebuild finishes. For that reason it should be done during off-hours when the affected systems are idle.
Paranoia is a Good Thing in a DBA - If they're worried about data loss I would have them do a proper test of the backups (restore them to a separate system and make sure the data is all there), and if they're still concerned then performing a full backup before rebuilding the indexes would be a reasonable precaution to take.