asked
Transaction Log is growing after index rebuild
I am using maintenance plan to rebuild my indexes. my Transaction Log is growing after index rebuild. I think if I can split rebuilding indexes in two or three part then i might be able to keep my transaction log size smaller (like 3 smaller). But I don't know how can I configure Maintenance Plan rebuild half of my whole indexes? Can anybody help me to achieve this?
Unfortunately maintenance plans are not designed to allow that amount of granularity. You would be better helped using a separate rebuild process like the scripts from [Ola Hallengren][1]. These allow much finer control of how your run rebuilds/reorgs. [1]:
http://ola.hallengren.com
+1 This (or the SQLFool script mentioned by @Thomas) will allow you to control the indexes that NEED rebuilding, rather than the MP approach. It does sound like you are rebuilding unnecessarily.
This behavior is quite natural while rebuilding large indexes, the log file continues to grow until the index operation is complete this to make sure that index operation can be rollback. You have to come up with a custom rebuild or reorganize scripts as @william suggested.
As well as the script mentioned by @William Durkin, there's also one from Michelle Ufford. The current latest version is at
http://sqlfool.com/2011/06/index-defrag-script-v4-1/ Something else you could do is put in a second schedule for Transaction Log backups so that they are backed up every minute instead of every hour during index rebuild processes. I've done this in the past, and it's quite effective at keeping the Transaction Log device's file size down!
It helps when you have lots of small indexes, as they each create a small transaction. It won't help when it's a large index where the transaction spans multiple log backups. If you're in the second situation, you're going to have to live with the larger transaction and associated log use.
Is the log growth causing you problems? There isn't any reason to be concerned about the growth unless it is affecting the running of the server. constantly chasing the growth and shrinking it again will more than likely cause you more problems. If you are fixed in using Maintenance Plans then I would recommend this book -
http://www.simple-talk.com/books/sql-books/brads-sure-guide-to-sql-server-maintenance-plans/ as an honest explanation of what they are good at and what you need to watch out for whilst using them.
7 People are following this question.
