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?
SQL Server Transaction Log Shrinking – When Should You Shrink Your SQL Server Transaction Log?
sql serversql server 2014transaction-log
Related Question
- Sql-server – Fix transaction log file after database update
- Sql-server – Shrink log file after configuring backups properly
- Sql-server – Shirking of Log file on Primary Server of Transaction Log shipping
- Sql-server – Unexpected Transaction Log Flush events and LOG_BACKUP errors
- Sql-server – Azure Backup SQL VM – Not shrinking log file after transaction log backup
- SQL Server 2017 – Cannot Shrink Transaction Log File Due to LOG_BACKUP Status
Best Answer
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/