How high Virtual Log File (VLF) counts kill your SQL Performance

High Virtual Log File (VLF) count

What is a Virtual Log File (VLF)?

The transaction log file is physically divided internally into several virtual log files (VLFs).

This number can grow base on how often the active transactions write to the disk and the auto-growth settings for the log file.

With high VLF counts, backups will run slower.

Some TSQL operations, such as UPDATE/DELETE will take longer.

By the way, this check is a part of our SQL Server Health Check service.

SQL Engine service takes a long time to start.  Replication/AlwaysOn/Mirroring/Log shipping and other operations – all suffer.

How to identify the issue?

You can get information about the VLF count using DBCC LOGINFO.

For newer SQL Server versions (SQL Server 2016 SP2 and later),  you may use the query below that uses SQL Dynamic Management Functions (DMFs).

  1. SELECT [name] AS 'Database Name',
  2. COUNT(l.database_id) AS 'VLF Count',
  3. SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
  4. COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'Inactive VLF',
  5. SUM(vlf_size_mb) AS 'VLF Size (MB)',
  6. SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
  7. SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'Inactive VLF Size (MB)'
  8. FROM sys.databases s
  9. CROSS APPLY sys.dm_db_log_info(s.database_id) l
  10. GROUP BY [name]
  11. ORDER BY COUNT(l.database_id)
Figure 1 – Query VLFs output.

When VLF under 100 – you can ignore

When between 100 – 200 – you can ignore, but better to fix

When above 400 – it’s getting urgent, so fix it.

When above 600 – slowdowns are happening, but it’s not easy to diagnose these. Fix.

When above 5000, fix now.

How to fix it?

  1. Fix database default growth settings.
  2. Shrink transaction log files, and pre-grow to set sizes.

More information

Pinal Dave – How to Reduce High Virtual Log File (VLF) Count?
Aaron Bertrand –  Virtual Log File Monitoring in SQL Server 2017

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *