Defragmenting Indexes Bloats Log Files

Question

I'm having difficulty "optimizing" indexes as a minimally logged operation. Before the index maintenance is performed, the database recovery model is switched from FULL to BULK LOGGED. Depending on the fragmentation percent, each index is the recipient of a REBUILD or a REORGANIZE (or no action is taken). After index maintenance is finished, the recovery model is reverted to FULL.

One database in particular is causing me some pain. The datafiles are about 64GB (including any free space). A defrag operation bloated the log file to 38GB, until it filled the logical drive. Then the level-17 alerts started rolling in.

I tried duplicating this in a test environment. Numerous attempts were made with different recovery models, index REORG vs REBUILD, different transaction isolation levels, read committed snapshot ON vs OFF, different index fragmentation levels, etc. Index REBUILDs on DB’s in the FULL recovery model always bloated the t-logs. No other testing variations did, however. This was frustrating because I could not duplicate what was happening in production.

What am I missing? How can I optimize indexes without bloating the log files?

UPDATE 09/23/2015
Not surprisingly, Ola Hallengren's name came up soon after I posted my question. Although I don't use his scripts, I am somewhat familiar with them. For those who are interested, there is a wonderful PASS session video--Ola is the presenter. Near the 48 min mark, an audience member asks a question similar to mine.

Answer 1

You can clear the log during the REORG process as its not done as a single transaction. The log bloating depends on index fragmentation level and amount of work done.

Again, REORG is a fully logged operation on all recovery models. https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx

Answer 2

Refer to the link Aaron Bertrand provided. There are a limited set of operations that are minimally logged, but reorg/rebuild aka defragmenting are not included. If you wish to minimize the effect, you'd have to do incremental reorgs as mentioned by Cibi or just allocate the needed space if this is expected to occur regularly.

I've had to do this before in production with incremental reorgs (start reorg, stop, backup log, start reorg, stop, backup log, rinse/repeat). This might be a good time to check if you need all the indexes that are giving you grief. Glenn Berry's diagnostic queries can help you find unused indexes which are being unnecessarily updated (read as fragmented). You can also try sp_blitzindex from Kendra Little which gives you loads of details on your indexes.