During our weekly index rebuilds (using Ola Hallengren's scripts), the log expands considerably. This isn't unexpected and there's plenty of space into which it can expand. I do have a task to look at minimising the growth but it's behind other things, including mitigating the growth.
The problem we actually have is further downstream. We have hand-rolled logshipping in place to keep some standby reporting databases in sync. It uses a set of PowerShell scripts to copy the log backups from the network share, restore them and then clean up. It usually works seamlessly but the weekend before last we had a disk space error on the reporting server. The ultimate cause of this was a 55gb log file that took 20 minutes to restore. This was greater than the 15 minute query timeout in the PoSh script and so it failed and the restore was rolled back. This meant that subsequent log files couldn't be restored, so they couldn't be deleted and so we ran out of space.
The quickest fix here was obviously to extend the timeout. After a bit of discussion, we thought that better than getting rid of it altogether. However, we're still getting the huge log files and we're concerned about hitting the new 30 minute timeout. Unfortunately changing the recovery model during the rebuilds isn't an option. We provide a managed service to a third party and what we do is 24 hour so there's always something happening on the servers.
What I'm thinking of doing next is to take the log backups more frequently during the index rebuilds. We currently backup every 15 minutes and I'm considering doing it every 5 minutes. My plan is to have a job running at 0355 on Sunday which runs sp_update_schedule to change the interval to 5 minutes and starts a WHILE loop to check whether the index rebuild is still running. When the rebuild job is finished, I'll run sp_update_schedule again to set it back to 15 minutes. Is this a wise idea?
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537