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?

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

First, go to this blog post here and find the VLF Count for the database.

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

Solarwinds

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 

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

Now based on this information, provide the log file name in the following script:

CHECKPOINT
GO
DBCC SHRINKFILE (N'WWI_Log' , 0)
GO

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

Now once again run this script to check if the current status of the VLFs.

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

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)

Solarwinds
, , ,
Previous Post
How to Get VLF Count and Size in SQL Server? – Interview Question of the Week #161
Next Post
How to Shrink TempDB Without SQL Server Restart? – Interview Question of the Week #163

Related Posts

Leave a Reply

Menu