Monitoring Virtual Log File Information

Monitoring transaction log usage is an important part of database maintenance. High numbers of Virtual Log Files (VLFs) within a transaction log negatively impact database recoverability, replication, roll-backs & backups. The FileSize and Status of the VLFs are also important for determining if the VLFs (individually) and transaction log (as a whole) are correctly sized.

(Note: To learn more about the mathematics behind VLF creation, read Paul Randal’s article at SQLSkills about changes to the VLF creation algorithm in SQL Server 2014).

For many years, the most common way to view a database’s VLF information was to use the Database Console Command: DBCC LOGINFO.

The LOGINFO command returns a row for each Virtual Log File in the Transaction Log. The FileSize column shows the size (KB) of each individual VLF. The amount of the transaction log that is currently in use can be determined by the number rows with a Status of 2 (0 = Inactive, 2 = Active & 4 = VLF exists on the Primary replica, but not on the Secondary replica).

One of the biggest problems with the LOGINFO command has been the difficulty of extrapolating the information to include all databases on the server, for use in reports, automated alerts and maintenance processes. Beginning with SQL Server 2016 SP2, there are 2 new Dynamic Management Views (DMVs) that can be used for monitoring the VLF information: sys.dm_db_log_info & sys.dm_db_log_stats.

The dm_db_log_info DMV is essentially an updated version of LOGINFO. A few key features of this DMV are:

• vlf_size_mb has replaced FileSize (MB often being an easier value to work with than the FileSize’s KB).
• vlf_active uses binary values (0 or 1) to designate if a VLF is active or not.
• As a DMV, the information can be directly queried and joined to other table data. For example, to query all databases on the server, and return any with VLF counts higher than 500, the log_info DMV can be cross-applied to the sys.databases table.

  SELECT [name], COUNT(l.database_id) AS 'VLF_Count'
  FROM sys.databases s 
  CROSS APPLY sys.dm_db_log_info(s.database_id) l 
  GROUP BY [name] 
  HAVING COUNT(l.database_id) > 500

The dm_db_log_stats DMV alternatively is a single line summary of the transaction log. A few key features of this DMV are:

• recovery_model (Simple, Full, Bulk Logged)
• total_vlf_count returns the number of VLFs within the transaction log, the same as using a COUNT() operation on sys.dm_db_log_info.
• active_vlf_count shows the number of VLFs that have been used since the last transaction log backup. This portion of the transaction log requires a tail-log backup in order to fully recover the database.
• active_log_size_mb shows the amount of storage space used by the active VLFs. If the active_log_size_mb is always significantly smaller than the total_log_size_mb, the transaction log may be over-sized and should be considered for shrinking/resizing.
• log_truncation_holdup_reason provides information on the cause for a log truncation operation to not complete. This is the same value that is given by the log_reuse_wait_desc in sys.databases.

The dm_db_log_stats DMV can also be joined with sys.databases for use in a monitoring dashboard or management report.

SELECT s.[name], l.[total_vlf_count], l.[total_log_size_mb] – l.[active_log_size_mb] AS [log_free_space_mb]
FROM sys.databases s
JOIN sys.dm_db_log_stats l ON s.database_id = l.database_id