Database re-indexing - best practices advice sought
Posted on 2004-11-16
I have a 15GB database which has a daily maintenance plan to optimise the tables by setting their fill factors back to 90%.
The problem is that it creates a huge amount of database transactions. The next transaction log backup after the reindex comes to 11GB!
What is the best way to run reindexing? Should I write a script to put the database into simple recovery model and then run the reindex? But if I did that, how would that affect the continuity of any potential restore operation I may need to run? Because I would be switching the database back and forth between simple and bulk-logged recovery model.
First question: What is the best way to make reindexing and transaction log backups co-exist without the reindexing operation blowing out the transaction log size?
Second question: Should I even be running a reindex every day or would once a week suffice? I'm thinking daily is best because the database is a major production system which is hit by many users every day.