Discussions specific to the VMware vSphere hypervisor
Post Reply
pufferdude
Expert
Posts: 158
Liked: 7 times
Joined: Jul 02, 2009 8:26 pm
Full Name: Jim
Contact:

SQL transaction log huge after running maint plan

Post by pufferdude »

This isn't a Veeam B&R question per-se, but I'm looking for expertise on what processes in SQL server affect the transaction log, because it's affecting my Veeam backups. Here's the background:

18GB-ish SQL db that Veeam backs up daily, and backs up (and truncates) the transaction logs every 15min. This all works great... except for immediately after my maintenance plan runs. Veeam will be happily backing up little 1-50MB transaction logs all day, then it gets one that's 15GB, which always happens right after the maintenance plan runs.

So I guess what I'm trying to find out is if this is normal, and just a consequence of my maintenance plan "touching" almost the entire db, or if it's an indication of something wrong? My maintenance plan does the following tasks:

- Check db Integrity (all user dbs, include indexes)
- Reorganize index (all user dbs, Compact large objects)
- Update Statistics (all user dbs, Scan type: full scan)
- History cleanup task (older than 4 weeks)
- Maintenance cleanup tasks

Given that, does anyone see anything "wrong" with my maintenance plan that's causing the log to grow so huge... or is this "just the way SQL server works" when you run maintenance plans like mine? Should I not be doing certain maintenance tasks?

Any advice appreciated.

PTide
Product Manager
Posts: 5731
Liked: 573 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: SQL transaction log huge after running maint plan

Post by PTide »

Hi,
- Reorganize index (all user dbs, Compact large objects)
Index reorganization is a fully logged operation but it should not cause the log to grow a lot. However, if the index is never reorganized, which is not the case I guess, it can generate a lot of log.
Are you sure that you don't perform index rebuild?

Thanks

pufferdude
Expert
Posts: 158
Liked: 7 times
Joined: Jul 02, 2009 8:26 pm
Full Name: Jim
Contact:

Re: SQL transaction log huge after running maint plan

Post by pufferdude »

I perform an index rebuild once a week, on Sat night via a different job. But the log grows by 15GB just by running the weekly tasks I mention above. I’m now thinking this might have just started when I upgraded from SQL 2014 to 2017, so perhaps it’s a bug.

Post Reply

Who is online

Users browsing this forum: No registered users and 10 guests