1

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.

  • Try ola Hallengrens index maintenance script ola.hallengren.com. I guess you are using a maintenance plan that rebuilds all indexes. Olas scripts will only rebuild the indexes that need it and that will produce much less logging. – James Anderson Aug 26 '15 at 15:35
  • @JamesAnderson, thanks for the input. I'm not using a maint plan. I wrote a set of my own scripts that I've been using for many years (back in 2009 IIRC). Like Ola's scripts, mine only defrag indexes that need it. – Dave Mason Aug 26 '15 at 15:42
  • 1
    Why do you think defragging is an operation eligible for minimal logging? I don't see it listed in the documentation. – Aaron Bertrand Aug 26 '15 at 17:47
  • @Aaron ALTER INDEX REBUILD is listed. Isn't that the operation the OP is using? – miracle173 Aug 29 '15 at 10:48
  • @miracle173 REBUILD != REORGANIZE – Aaron Bertrand Aug 29 '15 at 10:53
2

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

  • Thanks for the technet link/URL. I hadn't seen that before, and it clearly shows what is going on for my scenario. The "Other Versions" implies the article only applies to SQL 2005/2008/2008 R2.... – Dave Mason Aug 26 '15 at 17:06
  • Can you elaborate on "clear the log during the REORG process"? I presume you're talking about a t-log backup, right? – Dave Mason Aug 26 '15 at 17:10
  • @DMason the "Other versions" (well, the lack of modern versions being in that drop-down) simply implies that there isn't an identical topic yet for the 2012/2014/2016 documentation. This is because the doc set was reorganized for the SQL Server 2012 release, and not all topics/categories made it. You can find the same info here for newer versions. – Aaron Bertrand Aug 26 '15 at 17:49
  • @DMason - Yes, I mean taking the log backups. And that is the only way we can clear/truncate the t-logs. – Cibi John Aug 27 '15 at 15:36
  • @CibiJohn: Paul Randal mentioned the same thing on this forum. Trying to backup a t-log while an index reorg is happening sounds challenging. I'm not sure I'd want to go down that path. – Dave Mason Aug 27 '15 at 17:41
1

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.

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.