Alter Index REORGANIZE - Affect on Transaction Log

Question

Post reply

Alter Index REORGANIZE - Affect on Transaction Log

amcalpine

SSC-Addicted

Points: 439
More actions
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

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

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

calvo SSChampion Points: 12930 More actions · Answer 1

calvo

SSChampion

Points: 12930

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.