In my recent studies about Auto Growth, I took a look at one of our DB's at work and see the Transaction log has many Auto Growth events. My understanding is that the Auto Growth event is a blocking event and should be avoided. So my goal is to stop this recurring Auto Growth. Is this happening because the reserved Log space is too small and the transactions fill it up immediately triggering the Auto Growth? Also, In this DB, they are taking TLog backups every hour on the hour. Here is a pic from the Disk Usage Report. Thanks!
I think you'll find a good starting point here - http://blog.sqlxdetails.com/set-transaction-log-initial-size/
To summarize from the link:
- Set your transaction log large enough so it will not grow during normal operation. All update activity stops in the database when the logs grows, so you want to minimize log growths.
- For in initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
- Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
- The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS. You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process. While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
- In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
- In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
- Generally speaking, shrinking the log is discouraged. At some point, there was activity that caused it to grow to that size (index rebuild, etc.). There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size
You're right, your database is having to grow a lot at once which isn't ideal, the growth settings aren't adequate for what's happening there. By the looks of it I would guess that your auto growth settings are set to the default of 10% which is why you're seeing the growth changing each time.
What you want to do ideally is set the initial file size to a figure that's not going to need to be grown but you'll only really work this out from experience. For now, try setting it to whatever size your log file currently is.
For the auto growth amount it all depends on how active your database is but I'd definitely suggest a fixed figure rather than a percentage growth. Looking at the growth above, maybe set it to 256MB growth and see if this suits your needs. You can alter this if it's too large or too small.
Here's a quick link about these settings
Check out instant file initialization too for your data files.