SQL Server Auto-growth Handling 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?
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.