Sql-server – Impact of shrinking transaction log file after transaction log backup in SQL Server

sql serversql-server-2016transaction-log

In SQL Server, is there any performance issue if we shrink log file after taking the transaction log backup.
If yes, then please explain the reason.

Best Answer

Yes there may be performance issues if you continuously shrink the log files. When autogrowth event is triggered so that log file can grow for the database requirement all activities in database freeze to honor the growth request once this is acknowledged and done the processes resumes. Now if you daily shrink your log file it would grow frequently because you would run transactions and this frequent growth would frequently cause autogrowth events and thus frequent freezings. All this combined on a busy system can impact performance. This is why it is always advise to pre size your transaction log file to avoid as much less autogrowth as possible. Also note since Instant File Initialization is not there for log files the autogrowth would take some time as some more time would be spent on zeroing of the records.

Please read Importance of proper transaction log size management By Paul Randal

PS: It does not matter when you shrink transaction log file it always has some negative affect. You may shrink the log file when some unwanted transaction has bloated the log file and there is a space crunch but never make it a habit. Think of shrink button as option provided to you to get out of unavoidable circumstances/situations(like disk space issue, transaction log bloat) not for daily use.