How to Reduce High Virtual Log File (VLF) Count? – Interview Question of the Week #162

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)