Why this problem matters
Repeated small log growth events can create many virtual log files, or VLFs. An excessively fragmented log layout can impair recovery, restore, and log-management operations.
How to diagnose it
Use sys.dm_db_log_info to count total and active VLFs in each database. Interpret the count in the context of database age, log size, and growth history.
The query only reads VLF counts for online databases. A value that appears high is not, by itself, justification for intervention without considering log size and operation duration.
SELECT d.name,
COUNT(*) AS vlf_count,
SUM(CASE WHEN li.vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlf_count
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS li
WHERE d.state_desc = 'ONLINE'
GROUP BY d.name
ORDER BY vlf_count DESC;A safe solution approach
The preventive approach is to size the log for expected workload and use appropriate growth increments. Rebuilding an existing layout may require production operations and should be planned separately in a maintenance window.
Why continuous monitoring matters
VLF count usually deteriorates through accumulated growth events rather than one incident. moon can track log capacity and growth trends and provide early alerts through Slack, PagerDuty, or webhooks.
Frequently asked questions
Is there a fixed threshold for an ideal VLF count? Do active and total VLF counts mean the same thing?
No, one safe threshold does not fit databases of every size. Active count helps describe current log use, while total count describes the physical log layout.
How does moon help with this problem?
moon continuously observes SQL Server, PostgreSQL, and MongoDB signals, helping teams evaluate the problem as a trend instead of relying on a one-time check.