2

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:

  1. Back up the log file using:

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

  2. 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

Improve this question
3
  • 1
    Are transaction log backups being run on a scheduled basis? If not, that is step #1.
    – Tony Hinkle
    Feb 28, 2020 at 13:11
  • Log backups should take care of any inactive VLFs in your log. The problem could be a growing number of active VLFs which will not be backed up. This could be because replication is broken and the subscriber(s) are not being updated leaving the VLFs in the publisher still active.
    – Queue Mann
    Feb 28, 2020 at 15:11
  • @Queue_Mann - so how can I establish if broken replication is the problem? Replication Monitor does not indicate any problems, but is there a better method? Is there any other way of finding out what is using these VLF's?
    – CVee
    Feb 28, 2020 at 16:14

1 Answer 1

Reset to default
1

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. < 64MB will create 4 VLF’s = 64MB < 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 ).

Improve this answer
10
  • How can I find out what is causing this growth, please? These warnings have only appeared in the past 7 days and for the previous 2 years there have been very few warnings which have always been resolved by the above process. How can I find out what has changed, please?
    – CVee
    Feb 28, 2020 at 16:58
  • What about looking at a different angle - possibly reducing the alarm threshold for number of VLF's from 60 to a more reasonable number. As someone mentioned, when the number gets above 1000 is when slowness or issues can occur.
    – rvsc48
    Feb 28, 2020 at 18:33
  • Reducing the alarm level is going to hide the problem and it has only arisen within the past few days and yet not been a problem for years before. The other issue is that the Transaction Log files are now huge compared to the size they were 6 months ago (eg was 15Gb, now 60Gb and in another example was 130Mb and is now 50Gb). There is now a risk that we could run out of space on the server and so I really need a better solution, please.
    – CVee
    Mar 2, 2020 at 10:31
  • I have tried Ramakant Dadhichi suggestion (thank you) and run the script from that hyperlink and get "Cannot shrink log file 2 (dbName_log) because the logical log file located at the end of the file is in use." and then "MODIFY FILE failed. Specified size is less than or equal to current size." - please can somebody help?
    – CVee
    Mar 2, 2020 at 10:33
  • 1. If you have scheduled log backups running say every 30 minutes. Then you ll not run out of disk space due to log file growth unless a single transaction is that big and doesnt allow the low to trnscate. 2. If you have 100 GB disk for log file..make a thrshold abd then grow it to size big enough so as not to receive alerts for initial growth. Like 50GB. Now schedule log backups will take care of everything else.
    – Ramakant Dadhichi
    Mar 2, 2020 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.