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