1

My SQL 2012 SP3 Server is configured to run an Index Reorg job, via a maintenance plan, each weekend(not my call, it's policy, I promise!). I have a 30GB database(not including logfile) where the transaction logfile grows to alllllmost 30GB after index reorg's. There are several other databases in this instance that are of similar size but the logfiles are tiny, ranging from 17MB to 2.5GB. I have read through numerous forums and articles trying to determine why this particular logfile would grow so large. Alas, I have failed, which is why I'm now asking for enlightenment.

Here are some of the configurations:

Note, most of the configurations across the databases are the same(based on sys.databases).

  • Recovery Model = Full (on all databases)
  • is_FullText_Enabled = 1 (many, not all, of the databases have this enabled), there does seem to be some correlation here, the logfiles appear to be larger where is_FullText_Enabled = 1.
  • No replication/transaction log shipping of any kind in this instance.
  • Log backups run every 3 hours, which is part of the growth problem but not allowed to change schedule/frequency.

Here are some things I've tried:

  • Shrunk the log to 10MB to get the VLFs down, then grew it out to 24G.
  • Manually ran the index reorg a handful of times to see if fragmentation would go down and eventually stop growing the logfile but that did not help.
  • I even rebuilt the indexes(online via maint plan) and attempted a subsequent index reorg but the log grew back out again.
  • Shrank the logfile between reorg/rebuild attempts. Yes, shrink = bad, I know, bad DBA, bad!

So yeah. Index maintenance is logged, I get that, but why is there a large difference in logfile growths between databases? What am I missing? What could cause this logfile to grow so much larger than the rest?

I'm a newbie, make me more smarterer please and thank you. :)

  • Consider showing the tsql of your reorg plan to help understand the specifics of the job parameters and such. I assume you could not set the DB to simple mode and then re-index and then set it back to full, right? Even as a small test, I assume you couldn't see what the results are like that, right? So maybe in order, full backup with overwrite of BAK file, change to simple recovery mode, re-org job, change to full recovery mode, full backup with overwrite BAK, confirm log backup works or kick on off. Just a thought perhaps if nothing else. – Pimp Juice IT Apr 21 '18 at 18:54
  • The log file will grow large if transactions are not committed and they grow without commit then transaction will grow so simple mode might not be enough if re-org job has long running transactions. Do some research on that potentially as maybe the maintenance plan re-org job is not tuned for certain tables in your environment causing this issue. I'd do more homework and tracing transactions if I were you to see what transactions are causing log growth but workarounds like shrinking, recovery mode changes, shrink then grow or backup, etc. may help in the meantime. Meet business needs first. – Pimp Juice IT Apr 21 '18 at 18:58
5

In full recovery model, internal log truncation depends on you taking a log backup. If you wait three hours in between them, you'll have three hours of transactions sitting in there.

Index maintenance, as you've noted, is fully logged. If you truly perform some maintenance on every index in your database (or even most of them -- it's hard to say without knowing at what threshold you reorganize indexes), it's entirely possible for your log file to need to accommodate every one of those in between log backups.

Let's say in the 30 GB database you reorganize indexes at 5% fragmentation (a common tactic), and most of them have hit that level. You'll need to perform some action on all of those. In the other databases, if you have the same threshold, they may have far fewer indexes to touch. This could explain the discrepancy.

I understand that not much is under your control here, but if I had my DBA druthers, I'd want to

Using Ola's scripts is a no-brainer. You can set very specific levels to reorg or rebuild, which indexes to skip or include, and at what size you want to perform some activity. I get that this isn't your question, but should you choose to share this with management, I'd be a bad person if I didn't include it.

The other plus side? All the actions that Ola's scripts run get logged to a table, so you can look at what actually happened. That's the most important thing to have around for forensics if you wanna find out why something happened.

Hope this helps!

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.