question

Zahid avatar image
Zahid 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?
indexing
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+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.
1 Like 1 ·
Cyborg avatar image
Cyborg answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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!
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
Thanks Thomas, But my transaction log back is every 15 minutes and the rebuilding indexes takes nearly 50 minutes. That is transaction log gets backed up 3 times while indexes are rebuilding. it does not help me to keep the transaction smaller.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.