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. 🙂
Best Answer
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!