0

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?

Improve this question
2
  • 1
    Long running or very large transactions most likely
    – Charlieface
    Jan 18, 2021 at 0:53
  • 1
    In 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.
    – AMtwo
    Jan 18, 2021 at 1:26

1 Answer 1

Reset to default
2

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.

https://www.sqlshack.com/what-is-sql-server-virtual-log-file-and-how-to-monitor-it/

Improve this answer

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.