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