Myth – Shrinking Log Files is OK

Many times I have seen that DBAs feel like its OK to Shrink Log files in order to release space, and they perform this activity in SQL Agent job. In short, we are shrinking the few hundred GB of Log Files to few MBs on regular basis.

Well, I don’t feel like this is OK on regular basis. Since SQL Server takes a lot of pain in re-growing the Log Files from scratch. There is no concept of Instant File Initialization (or Fast File Initialization) in case of Log Files. Please read below blog post from Paul Randal for same.
https://www.sqlskills.com/blogs/paul/search-engine-qa-24-why-cant-the-transaction-log-use-instant-initialization/

So, each time a large transaction has to log its changes into Transaction Log file, it has to wait for the Log size to grow. This time of frame could be from just few milliseconds to many seconds, and can be observed from Performance counter ‘Log Growths‘ for object_name ‘SQLServer:Databases‘. Below is sample screenshot on my system.

This could get worse if the SQL instance version is 2012 or prior, and the autogrowth setting for Log file is not set properly. This would lead to high VLF counts which would impact transaction performance (especially in rollback of transaction) and database restores. We can use my Space-Capacity-Automation procedure script to get a nice tabular representation of Distribution of Log Files along with details like size, used space and VLF counts. Below is sample screenshot.

Friends, let me know what your experience says. Please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂