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