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)]

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!

The post SQL Server 2017 – DMF – sys.dm_db_log_info appeared first on .