Post reply

Transaction Log grows extremely during online index rebuild

  • April 12, 2015 at 3:12 pm

    #398312

    Hi,

    we are having a 200GB database with roughly 50GB of index data in it. The whole DB is in recovery model full.

    We are running SQL 2008R2 + SP2 Enterprise Edition on a 12 core / 192GB box.

    Recently we added a few more indices (so thats why we are having 50GB of index data now) and as a side effect the transaction log space went through the roof when we do an online index rebuild. Before, 120GB was enough but now 400GB is hardly satisfying the space requirement during the rebuild.

    What is the reason or how can I diagnose why an online index rebuild requires now 8x the index data size on the disk? As a result, our TL backups are also a few houndred gigs now.

    I appriciate any hints and ideas on what goes wrong here!

    Thanks, Tom

  • April 13, 2015 at 10:13 am

    #1790575

    Keep in mind that your tables are indexes if they are clustered.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • April 13, 2015 at 11:23 am

    #1790590

    Hi MadAdmin,

    can you explain that a little more and/or point me to some ressources that explains that?

    Thanks!

  • April 13, 2015 at 11:31 am

    #1790593

    Start here:

    http://www.sqlservercentral.com/stairway/72399/

    The stairway articles here are a great resource...but essentially a Clustered Index IS your table.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • April 13, 2015 at 11:45 am

    #1790596

    Thank you! Understood so far. However, what still puzzles me: if my database is 200GB in size (data + indices) - why can the TL grow >400GB when we do an index rebuild?

    Any guidlines on space calculations for TLs?

  • April 13, 2015 at 12:03 pm

    #1790603

    Especially when doing an ONLINE rebuild, it's a combination of the transaction you're running to rebuild the indexes (might want to break that down to smaller transactions) combined with the size of all the other transactions that are running while you do the rebuild, combined with the frequency of your transaction log backups. You could set those to run more frequently while doing the index rebuilds.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 13, 2015 at 1:43 pm

    #1790622

    Unfortunately online index rebuild generates a lot of log entries, in my case it was almost 3x index size :w00t: the only solution is switch to bulk-logged, but this may impact yours RPO objectives

    http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply