New answers tagged fragmentation
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).
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 ...
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 ...
I would not recommend the procedure that you mention in your question. If you did that, the newly built table would end up suffering the same amount of fragmentation as the original and you would be back to square one. I would recommend using Ola Hallengren's Index and Maintenance scripts to reduce the fragmentation of your indexes:- ...
Top 50 recent answers are included