SQL Server 2017 – DMF – sys.dm_db_log_info

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;

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

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!

Leave a comment

Your email address will not be published. Required fields are marked *