We do log backups every 3 minutes but despite that the transaction log has still managed to grow to 200+ gb on disk. Its almost completely empty after log backup. How do you know if your transaction log has become too big and needs shrinking? How do you determine the optimal size?
1Long running or very large transactions most likely– CharliefaceJan 18, 2021 at 0:53
1In addition to transactions preventing log reuse, your full backups will hold the log & prevent reuse. If you have a large database/long backups, and the database is "busy" during backups, this may be a time when your log grows significantly.– AMtwoJan 18, 2021 at 1:26
Transaction log sizing is important also because of VLF fragmentation which could wreck havoc on your restores and transaction log performance. Ensure your tlog VLF isnt heavily fragmented first I'd say.
Secondly a good way Paul Randal stated was to let your app run natively for a week, and see how big it gets. Make sure you do your regular maintenance on it and such.
Now maybe it gets so big because of ad hoc maintenance, queries, badly written queries, but to find the current good size that would work.