Get Rid of High VLF Easily

Spread the love

DBAs! I guess everyone know that huge number of Virtual Log Files (VLFs) in SQL Server can cause Backup/Restore & Database Recovery process slow. Even in rare cases, it can introduce slowness at transaction level.

https://sqlperformance.com/2013/02/system-configuration/transaction-log-configuration

Even we all are aware of it, it has been still a challenge to remove High VLF counts from SQL Server log files since it involved log of manual effort with Shrinking and re-growing the log files.

This is where my ‘Space-Capacity-Automation‘ open source project comes to your rescue. It has a parameter option @optimizeLogFiles that can help you optimize your log files by below below tasks:-

  1. Find All Log Files with count of VLF greater than @vlfCountThreshold (default = 500), and also the VLF count cross Ideal VLF count
  2. Shrink the Log File to release maximum space
  3. Regrow the Log File to the Original Size in chunk of Appropriate Auto Growth (check next point)
  4. Set the AutoGrowth to appropriate size by below logic:-

So with above AutoGrowth algorithm, the new VLF count generated for databases would never cross 512, thus Optimizing the Configuration of your Log Files.

Well that much said, let’s see how to use the Space-Capacity-Automation procedure [usp_AnalyzeSpaceCapacity].

Want to know how to use procedure, simply execute procedure with @help = 1 parameter

Help Table

If we switch to Messages tab in result set, then we find TSQL Syntax and some examples for each of the main parameters.

Help Syntax & Examples

As we can check, @optimizeLogFiles functionality of the procedure supports 4 other additional parameters namely @vlfCountThreshold, @DBs2Consider, @forceExecute and @verbose. To know more about each additional parameter, I would read [Parameter Description] from [Help Table]. Below are some examples on various usage of the @OptimizeLogFiles functionality:-

Below is sample output, that the code generates when @ForceExecute parameter value is set to default (0).

Move a short demo on how to use this procedure, kindly check below YouTube video:-


Get Rid of High VLF Issue @OptimizeLogFiles

Thanks Friends! I hope this has been helpful. Kindly raise issue on GitHub in case you find any error while using this automation procedure.

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.