December 9, 2013 at 8:13 am
Hi All,
All of my sql servers are in full recovery model. I am doing transaction log backups at 30 min intervals for my high volume databases; 2 hours for lower volume databases. Yet, I'm still having problems with the ldf files growing. Upon further research, the growth is occuring during our weekly maintenance plan....specifically during the step of rebuilding the index. So, a couple of questions:
1. is there a way to prevent the index rebuilds from expanding the ldf files?
2. should i be rebuilding the indexes weekly? This was a suggestion that we got from a microsoft trainer and we've already found that some of the other suggestions that this trainer gave were not good....so I'm starting to wonder if this is another.
Thanks for any input!
December 9, 2013 at 8:24 am
You can not prevent the index rebuilds from growing the LDF file. As for the second question it really is up to you if you can rebuild your indexes once a week. I don't know what you index fragmentation looks like so I can't answer that for you (and neither can someone else who hasn't see your usage pattern).
What I can say is that you should make sure that you are only rebuilding the indexes that need to be rebuilt and reorganize the other fragmented indexes (start with rebuild indexes > 30% and reorganize > 5%) and adjust those numbers as you see fit. Ola Hallengren has a great set of scripts for doing index maintenance (along with backups and other tasks) that allows you to set the rebuild and reorganize thresholds. Check the scripts here: http://ola.hallengren.com/
Another consideration is to give more space to your LDF file and/or drive and allow it to use the space when it needs it. Is there a reason you don't want to give it more space?
December 9, 2013 at 8:29 am
Rebuilding indexes is basically like dropping them and recreating them, and it's a fully-logged process, which is why it bloats your transaction log.
Keith has given you good advice there.
December 9, 2013 at 8:42 am
Thanks for the info. I will look into the Ola Hallengren sollution and hopefully that will prevent rebuilding indexes unnecessarily and maybe save some space.
Keith Tate (12/9/2013)
Is there a reason you don't want to give it more space?
No, i can give it more space....just wanted to be sure that it was justified before i do. after rebuilding the indexes, some of the LDF files are 75% of the size of the MDF file and that just doesn't seem right to me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply