Home / server / Questions / 266263
Accepted
Greg Hendershott
Asked: 2011-05-05 07:43:40 +0800 CST

DBA who is worried reorganizing or rebuidling indexes may cause data loss?

  • 772

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?

sql-server maintenance

3 Answers

  1. 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.

    • 22

  2. There is no risk of data loss from rebuilding or defragging indexes.

    • 6

  3. Reorganizing the indexes will take less time, and less effort from the SQL server thus they can be done in a weeknight type of instances. If you what you are saying is true, even reorganizing the indexes that have never been, may cause a larger impact on the server as well. Rebuilding the indexes will take a substantial amount of effort from the SQL server since they are dropped and rebuilt. Doing a rebuild on a weeknight isn't worth the risk of the server being busy with indexes and not serving the people using it.

    I agree with voretaq7, if he is that worried about working with indexes, try it out on the development or test servers first to see how the react.

    • 4