Post reply

Alter Index REORGANIZE - Affect on Transaction Log

  • May 4, 2011 at 4:29 am

    #250153

    Hi SQL Guru's

    I'm having a bit of a debate with another one of our SQL DBA's.

    We have a couple largish databases (200gb and 70gb) that have been delivered to us with some pretty heavy fragmentation levels. We have also a limited amount of log space to deal with the index maint. (I know , nightmare... I never implemented the build or design... ahhh)

    We have submitted a request for extra space on the log drive but I'm keen to define some extra log files on another drive to allow for us to do our index maint till we get extra space on the log drive.

    Anyway , our debate over how much the transaction log is hit during a Alter Index REORGANIZE when the database is in SIMPLE mode. One of the other dba's seems to think that the transaction log will hardly be hit becuase the REORGANIZE is done page per page and due to the SIMPLE mode being set then each bit of committed work will be cleared from the log as it processes the database pages.

    My concern is that a couple of tables are 100GB and that will only limited space for the log to grow (34gb) then we might end up blowing the log and the drive and causing futher issues. I had a good look about online but could find little info so basically my question is

    When in SIMPLE Mode and running Alter Index REORGANIZE is the transaction log hardly hit at all ?

    Any feedback would be greatly appreaciated.

    Thanks

  • May 4, 2011 at 6:19 am

    #1319683

    Index reorganization is always fully logged, no matter the recovery model. And yes, it could potentially make your log grow substantially.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • May 4, 2011 at 6:28 am

    #1319688

    Not 100% sure this applies to you but comming from Paul Randal, I'd listen!

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d8c242f7-939c-4d8c-89c7-3b69c5de2b03/

  • May 4, 2011 at 6:28 am

    #1319691

    Another usefull-ish refference for you

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

  • May 5, 2011 at 7:10 am

    #1320347

    Really great feedback , many thanks all for your help 😉

Viewing 5 posts - 1 through 4 (of 4 total)

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