Post reply

Index Rebuilds

  • October 4, 2011 at 2:31 am

    #243974

    Transaction log ran out of space when i was rebuilding indexes. I took a tran log backup and re ran the indexes rebuild and it failed again. Do i need to do a shrink right after the tran log backup to truncate the log?

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • October 4, 2011 at 3:54 am

    #1390037

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    Choosing a Recovery Model for Index Operations:

    http://msdn.microsoft.com/en-us/library/ms191484.aspx

  • October 4, 2011 at 4:00 am

    #1390041

    You probably want to shrink the log to the size it was before the reindex started.

    The reindex command can grow the log enormously. Be sure to take log backups during the rebuild (or add more disk space).

    Also, you should rebuild only the objects that really need a full rebuild and reorganize those that don't. Also, leave untouched the indexes that are not fragmented at all.

    Take a look at Ola Hallengren's index maintanance script. You can find it here: http://ola.hallengren.com/

    Edit:fixed url

    -- Gianluca Sartori

  • October 4, 2011 at 5:35 am

    #1390065

    ~Dev~ (10/4/2011)

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    Choosing a Recovery Model for Index Operations:

    http://msdn.microsoft.com/en-us/library/ms191484.aspx

    Actually makes no difference.

    Check the video demo section.

    Still a nice demo from Paul Randal on the topic

    http://us2.campaign-archive2.com/?u=729526cc5ec7737666e0a1893&id=98090171b3&e=b7a5726c5e

  • October 4, 2011 at 5:45 am

    #1390070

    I have restricted access to internet (Company's IT policy 😀 ). Would you mind summarizing it for us?

  • October 4, 2011 at 7:08 am

    #1390113

    Ninja's_RGR'us (10/4/2011)

    ~Dev~ (10/4/2011)

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    Choosing a Recovery Model for Index Operations:

    http://msdn.microsoft.com/en-us/library/ms191484.aspx

    Actually makes no difference.

    What do you mean 'makes no difference?' An index rebuild (not reorg) is minimally logged in bulk-logged and simple. That will reduce the log space used but won't help the size of log backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 4, 2011 at 7:20 am

    #1390120

    GilaMonster (10/4/2011)

    Ninja's_RGR'us (10/4/2011)

    ~Dev~ (10/4/2011)

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    Choosing a Recovery Model for Index Operations:

    http://msdn.microsoft.com/en-us/library/ms191484.aspx

    Actually makes no difference.

    What do you mean 'makes no difference?' An index rebuild (not reorg) is minimally logged in bulk-logged and simple. That will reduce the log space used but won't help the size of log backup.

    I obviously misunderstood something in Paul's video.

  • October 21, 2011 at 5:10 am

    #1397857

    GilaMonster (10/4/2011)

    Ninja's_RGR'us (10/4/2011)

    ~Dev~ (10/4/2011)

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    Choosing a Recovery Model for Index Operations:

    http://msdn.microsoft.com/en-us/library/ms191484.aspx

    Actually makes no difference.

    What do you mean 'makes no difference?' An index rebuild (not reorg) is minimally logged in bulk-logged and simple. That will reduce the log space used but won't help the size of log backup.

    I'm still not clear on this point even after rewatching the video and reading this => http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-(allocation-BULK_LOGGED-mode-locking).aspx

    I have a few guesses but I don't know the log internals. Got anything that would explain this point in more details?

  • October 21, 2011 at 5:20 am

    #1397863

    More details than Paul's videos? No, except maybe his training courses.

    Index rebuilds are bulk operations. They are fully logged in full recovery (entire page images are logged), they can be minimally logged in bulk-logged and simple (page allocations are logged)

    Per Paul's blog:

    Switching to the BULK_LOGGED recovery mode while doing an index rebuild operation WILL reduce the amount of transaction log generated, which is very useful for limiting the size of the transaction log file (note I say 'file', not 'files' - you only need one log file).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2011 at 5:27 am

    #1397869

    GilaMonster (10/21/2011)

    More details than Paul's videos? No, except maybe his training courses.

    Index rebuilds are bulk operations. They are fully logged in full recovery (entire page images are logged), they can be minimally logged in bulk-logged and simple (page allocations are logged)

    Per Paul's blog:

    Switching to the BULK_LOGGED recovery mode while doing an index rebuild operation WILL reduce the amount of transaction log generated, which is very useful for limiting the size of the transaction log file (note I say 'file', not 'files' - you only need one log file).

    Ok so just to put in in my own words with fictional numbers as I don't know those internals. In bulk mode :

    Page deallocation takes only 64 bytes in the log. But it takes the whole 8 K when doing the log backup. So in essence so save ±48% of the write operations in the log (including log backups). But you risk losing data if you need to do a PIT restore while the db is in BULK-mode.

    What I had understood from Paul's video is that there was no difference in the log backup size hence my comment about the no difference. Which is clearly wrong now that I get the full picture.

  • October 21, 2011 at 6:05 am

    #1397894

    Ninja's_RGR'us (10/21/2011)

    What I had understood from Paul's video is that there was no difference in the log backup size hence my comment about the no difference.

    There won't be a decrease in the size of the log backups. They may even increase in size. However that's the backups. The usage of the actual log file itself will decrease.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2011 at 7:18 am

    #1397950

    I had the same problem for the index rebuild.

    A possible solution was to reorganise the index in place of rebuilding.

    And when reorganizing, take log backups frequently...

    I had the help of the Ninja and GilaMonster 🙂

    http://qa.sqlservercentral.com/Forums/Topic1154939-146-1.aspx

Viewing 12 posts - 1 through 11 (of 11 total)

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