I have a database that we currently run transaction log backups throughout the day, every 30 minutes to be exact, and we run a full backup each day at 2am.
Every Saturday at 3am, we have a job setup to rebuild the indexes on all tables.
That being said, doing the index rebuild causes our transaction log to grow greatly. I'm toying with different ideas to alleviate the extra drive space needed (approximately 25gb post re-index).
I was considering setting the database recovery model to simple prior to the rebuild task, to prevent all of the index rebuilding from being logged, and then setting it back to full once the rebuild is complete.
Is anyone else using this method? Or can anyone provide insight/advice as to why this may be a bad idea? Or any tips about how to handle huge log files while performing db maintenance tasks?
Best Answer
There are a number of considerations here:
One approach I've taken in the past is to incorporate log backups in to the reindex/rebuild scripts. Record the log size and free percentage before processing each table, check free percentage and size afterwards. If less than x% of space is free or if log growth has occurred, backup the log.