0

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • I just want to shrink for the once and I want to know that after shrinking what happened in the database so its performance degrades. What actually causes the degradation of the performance?
    – Himanshu Tejwani
    Commented Jun 25, 2018 at 12:52
  • @HimanshuTejwani You may shrink once, after shrinking the space will be released and when you run queries the autogrowth event would be triggered and space which you freed would be retaken and this process of retaking will add some extra overhead to queries. This is what I wrote in my reply. Basically if log file will grow again there hardly seems and sense behind shrinking it. Rest all is in my answer
    – Shanky
    Commented Jun 25, 2018 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.