A transaction log can look healthy until recovery slows and every growth event adds more fragments. Monitoring VLF counts gives you an early view of that problem and a reason to review growth settings.
Why VLF Counts Matter
SQL Server divides each transaction log file into virtual log files, or VLFs. The engine writes through them in sequence and reuses inactive portions when log truncation is possible. A large number of VLFs gives recovery, backup, and log management more pieces to examine. The number alone does not diagnose every slow operation. It does tell you whether the log has grown through a pattern worth investigating.
I check VLF counts early when I inherit a server with unusually large logs. A file that grew in small increments over years deserves a closer look than a file sized for its workload. You need the growth history and current settings before changing anything. VLF counts without context are a clue, not a repair plan. Is your log growing because the workload needs space, or because truncation is stalled?
Check VLF Counts Across Databases
The database scoped function sys.dm_db_log_info exposes one row per VLF. Call it with a database identifier and group the rows to get a useful count. The following query covers online databases and leaves system databases visible, since tempdb and msdb can have their own stories. Run it with the permission required by your SQL Server version. Review errors separately when a database is inaccessible.
I put the result beside log size and growth settings. That turns an isolated count into an operational picture. A very small count in a large log and a very large count in a modest log lead to different discussions. There is no magic number that fits every workload. Trend VLF counts against the server’s own history and recovery behavior.
SELECT d.name AS database_name,
COUNT(*) AS vlf_count
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS li
WHERE d.state_desc = N'ONLINE'
GROUP BY d.name
ORDER BY vlf_count DESC;Inspect Active and Inactive VLFs
The detail rows matter when a database has a surprising count. They show the sequence, size, and active status of each VLF. Read them in file and offset order. A long run of active VLFs near the end of a large log is a different problem from many inactive fragments left by old growth events. Do not infer that a log backup will instantly make every VLF reusable.
Use the detail query for one database at a time. Substitute a database name that exists on your instance. The status column is an observation at the moment of the query. A busy system changes while you are reading it. Save a timestamp with any investigation notes, then check the log reuse reason before prescribing a file operation.
SELECT DB_NAME(li.database_id) AS database_name,
li.file_id,
li.vlf_begin_offset,
li.vlf_size_mb,
li.vlf_active
FROM sys.dm_db_log_info(DB_ID(N'master')) AS li
ORDER BY li.file_id, li.vlf_begin_offset;Find the Growth Rule
Read sys.database_files from the database itself when you need its exact growth setting. The growth value means a percentage when is_percent_growth is one. Otherwise, the value is stored in 8 KB pages. A display that omits the unit can send a well-meaning change in the wrong direction. Growth by a small percentage can produce tiny steps early and huge steps later.
For a quick server overview, sys.master_files exposes every database file. Keep the numeric value and unit together in your report. Also check max_size, because a sensible increment cannot help when the configured limit stops growth. Auto growth is a safety net for bursts. It should not be the normal capacity plan for a known workload.
SELECT DB_NAME(database_id) AS database_name,
name AS logical_name,
size * 8.0 / 1024 AS size_mb,
growth,
is_percent_growth,
max_size
FROM sys.master_files
WHERE type_desc = N'LOG'
ORDER BY database_id, file_id;
Understand Why Space Is Held
Before touching a large log, inspect log_reuse_wait_desc in sys.databases. It names the current reason that log space cannot be reused. A backup requirement, an active transaction, replication, or an availability feature calls for different action. Fixing file growth does nothing for a transaction that has remained open. Repeatedly shrinking the file merely schedules the next growth event.
I look for the workload’s normal high-water mark before proposing a target size. Keep enough room for planned index maintenance, batch loads, and the longest expected transaction. If a sudden event explains one large spike, document it and decide whether that capacity is still needed. Guessing from today’s low usage is how a log shrinks on Friday and grows on Monday.
Set Deliberate Growth Increments
Choose a fixed increment that fits the database and storage layout. Recent SQL Server versions changed some VLF creation behavior, so an old blanket formula is a poor substitute for inspecting the current file. Size the log for ordinary peaks first, then set a growth increment for exceptional demand. Make the setting consistent with storage alerts and free-space monitoring.
Changing FILEGROWTH is a configuration action, not a VLF cleanup operation. Use the actual logical file name and a reviewed increment. The example below shows syntax only; the number is a setting to choose for your environment, not a universal recommendation. Test the change in your normal change window and record the previous value.
ALTER DATABASE [YourDatabase]
MODIFY FILE
(
NAME = N'YourDatabase_log',
FILEGROWTH = 512MB
);Treat Shrink as a Special Operation
Shrinking a log can be justified after a one-time event that permanently changed capacity needs. It is not a routine maintenance job. First resolve the reuse wait and confirm that active VLFs allow the end of the file to be released. Under the full recovery model, take the required log backups according to your recovery plan. Then shrink once to a reviewed size and leave room for normal peaks.
I have seen automated shrink jobs turn a steady system into a growth-event machine. The storage returns briefly, the file grows again, and the VLF count climbs. That is a rather elaborate way to stand still. If a shrink does not reduce the file, inspect VLF placement and reuse state rather than repeating the command blindly.
Keep a Small Trend of VLF Counts
Capture the VLF count, file size, used log space, growth setting, and log reuse reason on a regular schedule. A single snapshot tells you where the server stands. A trend tells you whether a new deployment or workload has changed its behavior. Keep the collection query light and run detailed VLF listings only when the summary changes.
A useful alert describes the database, its recent change, and the action to check. Avoid a page that says only that a count crossed an arbitrary threshold. The reader should know whether to inspect long transactions, backup failures, or disk capacity. With that context, monitoring log growth becomes a practical capacity habit instead of a monthly surprise.
Related reading on this blog: Query to List Active and Inactive VLF and SQL Server 2022: Managing Virtual Log Files.
A high VLF count is not a cleanup command, it is a reason to inspect how the log grows.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
