DBAtlas · Database operations atlas

Show the VLF (Virtual Log File) count

SQL ServerBackup & Log

Command

-- SQL Server 2016 SP2 / 2017 and later:
SELECT DB_NAME(database_id) AS db,
       COUNT(*)             AS vlf_count,
       SUM(CASE WHEN vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlf
FROM   sys.dm_db_log_info(DB_ID(N'Customer'))
GROUP BY database_id;

-- For older versions: DBCC LOGINFO('Customer'); (row count = VLF count)

What it does

Returns the number of virtual log files (VLFs) inside the transaction log. Too many VLFs degrade startup/recovery/log backup performance.

When to use it

When the log is slow or database startup takes long; when reviewing the log growth setting.

Caution

Hundreds/thousands of VLFs are a warning sign; the fix is to shrink the log and grow it again in suitably large increments (e.g. 8GB steps). Query only; no risk.

Open in DBAtlas →

Related