The release of Microsoft SQL Server 2017 brought a lot of very interesting new features. One of them is the addition of DMF sys.dm_db_log_info. It allows having a look at the structure of the transaction log without using the undocumented DBCC LOGINFO command. 

DMF – sys.dm_db_log_info

It returns VLF information of the transaction log file and it gives some additional information compare to the DBCC LOGINFO command. To use the DMF, you need to pass a single int parameter called @DatabaseId. 

Let’s execute the below queries to see the internal structure of the transaction log using old method DBCC LOGINFO and using the new DMF;

--old way using the undocumented command
DBCC LOGINFO;

--New way using the DMF in SQL Server 2017
SELECT * 
FROM sys.dm_db_log_info(DB_ID(DB_NAME()))

Here is the output;

To Capture all the databases log file size and the number of VLFs in one shot , you can run the below query

WITH [DatabaseCount] AS(
SELECT 
DB_ID(dbs.[name]) AS DatabaseID,
dbs.[name] AS [Database Name], 
CONVERT(DECIMAL(18,2), dopc1.cntr_value/1024.0) AS [Log Size (MB)]
FROM sys.databases AS dbs WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS dopc  WITH (NOLOCK) ON dbs.name = dopc.instance_name
INNER JOIN sys.dm_os_performance_counters AS dopc1 WITH (NOLOCK) ON dbs.name = dopc1.instance_name
WHERE dopc.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND dopc1.counter_name LIKE N'Log File(s) Size (KB)%'
AND dopc1.cntr_value > 0 
)
SELECT [Database Name], [Log Size (MB)],COUNT(b.database_id) AS [Number of VLFS] 
FROM [DatabaseCount] AS [DBCount]  
CROSS APPLY sys.dm_db_log_info([DBCount].DatabaseID) b
GROUP BY [Database Name], [Log Size (MB)]

When you are dealing with a database slow recovery problem, you may find that a large number of VLFs are contributing to slowness. The below query gives you detailed information about the VLFs utilization.

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 'In-active VLF',
	SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active 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
GO

The DMF makes DBA life a lot easier when it comes to review and store the database log file information for the troubleshooting.

Thanks for reading!