The VLF growth rules say how many virtual log files one log growth adds: one up to 64 MB, eight up to 1 GB, sixteen above. This post measures them on a demo database, shows a log with 68 VLFs from 1 MB steps, and repairs it by shrinking and growing once.
Active and Inactive VLFs: List Them for Every Database
To list active and inactive VLFs for every database, count the rows of sys.dm_db_log_info by vlf_active and read the log reuse wait next to them. This post builds a demo log with 24 VLFs, shows how an open transaction and small autogrowth steps add more, and fixes the count with one shrink and one regrow.
SQL Server Cluster Resource in Online Pending Too Long
A SQL Server cluster resource that stays in Online Pending for minutes can be waiting for database recovery. This post reads the cluster events and log lines of one case, finds an 8 minute gap, and traces it to a huge number of virtual log files that a log backup and a shrink fixed.
How to Get VLF Count and Size in SQL Server? – Interview Question of the Week #161
How do you get VLF Count and size in SQL Server? Newer versions add a Dynamic Management Function for it. Here is the query, plus links to my older methods.
SQL SERVER – PowerShell to Count Number of VLFs in SQL Server
Too many VLFs can hurt performance. Here is a simple PowerShell script that counts the VLFs in every database on one or many SQL Server instances.
Why a Second Log File Does Not Make Anything Faster
Adding a second log file feels like a speed fix, but it is only a spare room. Here is how to watch it work and how to retire it.
SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file
Too many virtual log files are bad for your LDF. Here is a simple script to reduce the virtual log files right away with a log backup and a shrink.