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 ?.

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

3 Answers 3

Reset to default
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).

Improve this answer
2

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

Improve this answer
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

Improve this answer
2
  • Thanks, then why does it needs the log ?
    – Ofiris
    Oct 18, 2014 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, 2014 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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