One of the new features introduced by Microsoft in SQL Server 2017 is the new DMF sys.dm_db_log_info. It helps to take a look at the structure of the transaction log file without using the undocumented DBCC LOGINFO.
As we all know, Transaction Log Files are internally divided into small virtual log files called VLF/VLFs. We can’t directly configure the size or number of VLFs which is actually affected by the number of activities. The number one reason is when we add a log file or the size of the log file get increased.
Having a large number of log files(a thousand or more) can affect the backup performance, restore, and database startups. Based on the size of the log space increase (due to the autogrowth event) the new VLFs created are as follows:
Here we can see if the autogrowth of the database is set to a lower size it can create numerous VLFs, which can affect performance.
This new DMF sys.dm_db_log_info will return VLF information of the transaction log file. We can use DB_ID to get information from a specific database or use null or default to get information from the current database. This DMF replaces the DBCC LOGINFO statement from earlier versions.
The following command could be used to find the number of VLFs in the log files, which we can use as a parameter to determine if it is affecting the performance.
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) > 1
Output:
In a production environment, we can target the log files that have more than 1000 VLFs. Using this we can create an agent job to notify the DBA when the log file crosses a certain limit and take action accordingly.
We can also use the following query to determine the status of last VLF before running the shrinkfile command on the log files to determine if it can be shrunk:
SELECT TOP 1 DB_NAME(database_id) AS "Database Name", file_id, vlf_size_mb, vlf_sequence_number, vlf_active, vlf_status
FROM sys.dm_db_log_info(DEFAULT)
ORDER BY vlf_sequence_number DESC
Output:
Below is the query we can use to see all columns of this DMF. As we can see it is showing 1 column for each VLF. This ‘test’ database has all the detailed information and the database id here is 5.
SELECT * from sys.dm_db_log_info(5)
The important columns here are as follows:
Vlf_size_mb: Size of the VLF in MB
Vlf_active: Indicates if VLF is in use or not.
0- VLF is not in use
1- VLF is active
Vlf_status: Shows the status of the VLF.
0- VLF is inactive
1- VLF is initialized but unused
2- VLF is active.
Reference