MS SQL Log shrink

[coolsport00]

Before posting, I searched a bit for the subject I'm inquiring on, but didn't find specifically what I am wanting to know. This article was close though:
http://forums.veeam.com/veeam-backup-re ... 17018.html

1. Why doesn't VB&R shrink logs post-truncating? (I'm not too entirely knowledgeable about SQL so don't know the implications of having a b/u solution auto-shrink logs)
2. Is SQL Log shrink a projected future feature enhancement? Maybe a shrink doesn't need to occur after every b/u is run, but if this is eventually added as a feature, maybe a schedule is added as well to maybe run a shrink task monthly.

Any insights, etc. are appreciated.

Thank you.

Shane
(twitter: @coolsport00)

[marco.horstmann]

Hi Shane,

because e.g. many DBAs configure specific log file sizes for prevent fragmentation of a log file.
Years ago I see this as a problem too but in my opinion today it doesn't matter. The Log File will
only get as big as your changes will be between two truncates.

Regards
Marco

[coolsport00]

Thank you for the reply Marco. Not sure you actually answered my query. Truncating logs doesn't shrink. Storage is still retained on disk after truncating is complete. Still looking for a good answer as to why shrinking isn't performed in backups. And, to be fair/honest, it's not just VB&R... no b/u solution to my knowledge does it. My question is why? What's the big deal?

Thanks.

[alanbolte]

Hello Shane,

I'm not a DBA by training, but let me expand on what Marco said with my understanding of the topic:

1. Log file auto-growth can introduce fragmentation of the log file that reduces database performance. Better to pre-allocate and not shrink.
2. Log file auto-growth requires some I/O, so that's another reason you're better off pre-allocating and not shrinking.
3. Shrinking requires some I/O, may as well avoid that if we can.
4. The biggest reason - if your database is operating correctly the shrink will accomplish nothing in the long run. That is, the log data will generally grow no larger than a certain amount between truncations (backups), and you don't want the disk to run out of space between backups because you used that disk space for something other than the log file, so you might as well reserve that space for the log data. What better way to reserve the space than to just not bother shrinking the file?

This video goes into some nice detail about how the log file works, and where fragmentation from auto-growth can come from.

[marco.horstmann]

Hi Alan,

thx for clarifying my post. I couldn't do it better.

I'm from my historical knowledge a storage guy. I often
discussed with customers and application vendors about
optimization for performance before I started at Veeam.

And this points Alan wrote I had often heard from application guys.

Regards
Marco