2

MSDN states:

ALTER INDEX REORGANIZE; however, log space is required.

The database recovery mode is Simple, does that implies the log space is actually NOT required ?.

  • 3
    No, log space is still required to support rollback / undo operations. – Aaron Bertrand Oct 18 '14 at 14:47
3

To answer more concisely than the existing answers:

REORGANIZE produces the same amount of log with SIMPLE and FULL. It's just that SIMPLE almost immediately makes that space available again.

Except if something is preventing log truncation such as an open transaction (unrelated to the session the REORGANIZE runs in).

1

Database recovery mode SIMPLE never means that log will not be created. SIMPLE recovery model just ensure automatically reclaiming of log space once operation is complete/committed.

ALTER INDEX REORGANIZE operation creates log and for this it needs log space regardless of database RECOVERY model.

Learn more about recovery models in SQL Server http://msdn.microsoft.com/en-us/library/ms189275.aspx

  • Thanks, then why does it needs the log ? – Ofiris Oct 18 '14 at 10:03
  • 1
    Please read my comment it needs logs because it writes information about operations it does during reorganize as I said every thing would be logged. Logging and recovery is very big concept you can read more why Logging and transaction log is required technet.microsoft.com/en-us/magazine/2009.02.logging.aspx – Shanky Oct 18 '14 at 10:37
1

You missed the exact point what books on line was trying to mention it states that

The following index operations require no additional disk space: ALTER INDEX REORGANIZE; however, log space is required.

If you read complete article it was trying to point out the commands or operations in SQL Server which would require additional disk space and others that would not. It was trying to do so because when Index is REBUILT(with ONLINE OPTION) it used extra space because during first phase of rebuild system metadata preparation happens which creates the new empty index structure then snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency. This snapshot would take space this is what books online meant by extra space but in Reorganize of Index no such operation happens no such snapshot is created just it only requires 8KB of additional space in the database which is not much of a concern.Also note that reorganize operation is fully logged.

Index reorganize is always online operation while index rebuild can be both ONLINE(present only in enterprise edition) and OFFLINE.

The database recovery mode is Simple, does that implies the log space is actually NOT required ?.

No, recovery model affects the way things are logged and the recovery it does not means that there would be no logging. Every operation in SQL Server is logged in some way or the other and recovery model defines way logging will happen. In simple recovery model which is almost same as Full logs are truncated when transaction commits or when checkpoint is given this happens automatically when transaction commits or also when Log file reaches 70 % of its size. The only thing that would stop log truncation is if some long running transaction still requires that portion of log. While in full recovery you have to take transaction log backup to truncate the logs

Hope this is clear.Please let m know if you don't understand few points

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.