2

I have this scenario:

  • DB Log with initial size of 1193 and auto growth by 100 MB unlimited
  • Everyday at 8am, I have 20 auto-growth events firing per second which is my problem

Is the correct solution here is to adjust the size of log file so that auto growth will not fire frequently? After adjusting the db log file, should I also change the auto-growth size to like 500mb to at least limit the number of auto-growth event when the log file reach the max file size?

3

You can reduce the number of growth events by increasing the growth factor, but be careful. Log files cannot take advantage of instant file initialisation, so if you increase the growth factor too high you can cause delays during auto-growth events.

You also need to consider the number of VLFs that will be created based on the growth increment and the number of growth events. Check out this article by Kimberly Tripp for VLF info.

If your log file is constantly increasing in size, this growth ongoing so you should consider increasing your growth factor to reduce the number of events if they are impacting performance.

If your log file is being shrunk every day, this growth is repeating, meaning it wouldn't keep increasing the log file size each day if you grew the log to a reasonable size. Stop the process that shrinks your log file and grow the log file manually to a suitable size to mitigate the high-frequency log growth events.

2

Please take a moment to think why these allocations are happening. SQL Server's log is designed to re-use disk space once the information currently held there is no longer required. This suggests a few scenarios for your case.

Perhaps the log disk space is not being released for re-use. This is possible if the database is in full recovery mode and log backups are not taken. Without the log backup SQL Server will retain past transaction log (t-log) data inside the log files so it can recover from a failure. Once the log backups have been complete SQL Server "knows" that past log data is safely in your backup repository and it can release the corresponding disk space for re-use. If you are in full recovery and not taking log backups you should be. Make them frequent enough to match your RPO. Every 15 minutes is not unusual.

It may be the transaction log file is being shrunk. During an auto-growth event SQL Server requests disk space from the operating system (OS) and adds that space to the t-log. During a shrink event the reverse happens and SQL Server hands back to the OS some of the disk space it has reserved. If you are shrinking t-log files stop doing it. It is self-evident that the space is needed so should not be handed back to the OS.

You may genuinely have more data each day that requires additional t-log space to process. Therefore the t-log files have to expand to accommodate that data. Then your suggestion of expanding the files and increasing the auto-growth increment makes some sense. There is an alternative, however.

Rather than process all the data within a single transaction you could process it in batches. Each batch will be its own transaction and use some t-log disk. When a transaction is committed the t-log space becomes available for the next batch to use (when in simple recovery) or can be made available by taking a t-log backup (when in full recovery). There is a trade-off between development complexity in your 8am process and keeping the t-log size under control. That's for your management and users to agree.

There's a lot of information and further links here.

0

Is the correct solution here is to adjust the size of log file so that auto growth will not fire frequently? After adjusting the db log file, should I also change the auto-growth size to like 500mb to at least limit the number of auto-growth event when the log file reach the max file size?

Yes, depending on application work-load you can change the auto-growth setting to accommodate more space in LOG for the operation, it can NOT guaranty (particularly when RECOVERY = FULL) to prevent Auto-growth fire frequency, it depends on LOG File truncation which can only happens with LOG BACKUP. i.e. at certain time log size being utilized 500MB, if there is no truncation (Log backup) performed, server try to increase it to 1000 MB

Aside from Auto-Growth setting, you may want focus on LOG Backup schedule

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.