Question: How to Reduce High Virtual Log File (VLF) Count?
Answer: Last week, I posted an interview questions about How to Get VLF Count and Size in SQL Server?. Once the blog post is out, the most frequently asked question received was was as follows: Once we know that we have high Virtual Log File (VLF) issue, how can we reduce the count of the same?
First, go to this blog post here and find the VLF Count for the database.
Next, let us get the log file information for the database for which you want to reduce VLF counts.
If your database is in Simple Recovery mode, you can move forward to next step. If it is not in Simple Recovery mode, you may have to take Log Backup before you can go to next step.
USE [WideWorldImporters]; GO EXEC sp_helpfile; GO
Now based on this information, provide the log file name in the following script:
CHECKPOINT GO DBCC SHRINKFILE (N'WWI_Log' , 0) GO
Now once again run this script to check if the current status of the VLFs.
You will notice that VLF value is now gone to a lower number as all the inactive VLFs are removed from the log file.
Once the VLFs are reduced to a lower number, it is recommended that you set the autogrowth value for your log file to a higher value so you can prevent high VLF issue in the future. You can do that b the following script here:
ALTER DATABASE [WideWorldImporters] MODIFY FILE ( NAME = N'WWI_Log', SIZE = 1024MB )
Let me know have you ever faced issue with the VLFs? How did you fix it?
Reference: Pinal Dave (https://blog.sqlauthority.com)
May wanna add some caveats about iteratively growing the log depending on version as well as the caveat that post sql 2014, if the t-log is over 4.5gb, only 1 vlf will be created with your autogrowth setting.
Hah ran into this again… Your script is also wrong, if you’re trying to change growth you need to set “FILEGROWTH” not the full size of the log. This article needs to be cleaned up with post sql2014 growth considerations.