VLF count suddenly growing and unable to reduce it Owing to reductions in IT resources, I am now finding myself having to manage this with limited knowledge and so am seeking advice, please.

We have 2 SQL servers with replication of multiple databases between them. The high VLF count is coming from the "parent" and started to be reported on 21st February 2020 and has continued to grow on odd days, despite my efforts. These servers are in production and in use 24/7.

The servers are running SQL Server 2008 R2 (and yes I do know that is out of support, but there is no money at all to do anything about that) and are in Full Recovery model.

There are 3 databases that are warning of high VLF count and so what I have tried to do it this:


Back up the log file using:

USE dbName
GO
BACKUP LOG dbName TO DISK='M:\dbNamelog2020_02-24.bak'
GO

Shrink the log using the SSMS GUI: 


right click dbName 
choose Tasks > Shrink > Files 
change the drop down from Data to Log and leaving all other choices as their default



Taking one of the databases as a specific example the VLF count has gone from:   


53 on 21/2/20 
85 on 24/2/20 
101 on 26/2/20 
117 on 27/2/20 


...and then I ran the above shrink and now it is 103. 

The system I have inherited alarms when the threshold for VLFs is over 60, so apparently there is still a problem.

I have followed the same process with 2 other databases and with similar results (i.e. the VLF count remains above 60).

Now the Log file for my above specific example today shows (via SSMS): 


Currently allocated space = 59750.38Mb 
Available free space = 54423.27Mb (91%) 


The Log file has an Initial Size of 59,751Mb and Autogrowth is set to grow By 10000 MB, restricted growth to 2097152 MB.

I hope I have given enough detail that either someone can spot a stupid mistake or illuminate how I can progress with this issue. I thought I read somewhere that it should be possible to work out what is creating these VLF's (especially if they are due to incomplete transactions, such as replication), but if that is true then I cannot discover how to actually identify what is creating them.

Any and all advice will be much appreciated.

Many thanks,
Caspar
The VLFs grow when the log grows 
When VLF’s are created, either by autogrow or a manual grow; a certain number of VLF’s are allocated according to the size.
&lt; 64MB will create 4 VLF’s
= 64MB &lt; 1GB will create 8 VLF’s
and > than 1GB will create 16 VLF’s.

This was prior to 2014. From 2014 SQL Server onwards this has changed and can read in more details here : Understanding increasing number of VLFs 

A few hundred of VLF is actually not that a big issue and you can re-configure your threshold. To fix this you can shrink the log file to the lowest possible and make it grow to a bigger size at once and then allow autogrowth if required. More on this can be read here : 

Fixing vlf issue

VLFs will always increase and only when huge in number will cause performance issues (thousands ).