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.
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-Automationprocedure 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 🙂
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.