Virtual Log Files (VLFs) are an important aspect of SQL Server’s transaction log management. They play a crucial role in the performance and recoverability of your database. In this blog post, we will explore how to get VLF count and size information using the newly introduced Dynamic Management Functions (DMF).

In earlier versions of SQL Server, there were different methods to detect and reduce VLFs. However, with the release of SQL Server 2017 and later versions, a more efficient and comprehensive script is available to retrieve VLF information.

Let’s take a look at the script:

SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'Inactive VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'Inactive VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC

This script retrieves the VLF count, size, and other relevant information for each database in your SQL Server instance. It calculates the active and inactive VLF count, as well as their respective sizes. The results are sorted in descending order based on the VLF count.

It’s important to note that this script is only compatible with SQL Server 2017 and later versions. If you have upgraded to SQL Server 2017 or a newer version, it is recommended to use this script instead of the earlier methods.

By understanding the VLF count and size in your SQL Server databases, you can optimize the performance and manage the transaction log effectively. Monitoring and maintaining an appropriate number of VLFs can help prevent issues such as excessive log growth, slow recovery times, and performance degradation.

In conclusion, the newly introduced Dynamic Management Functions (DMF) provide a convenient and comprehensive way to retrieve VLF count and size information in SQL Server. By utilizing this script, you can gain valuable insights into your database’s transaction log management.