One of the new features included in SQL Server 2017 that Microsoft has detailed here is a new DMF called sys.dm_db_log_info

This new DMF is intended to replace the (not so) undocumented DBCC LOGINFO command. I say undocumented as I’ve seen tonnes of blog posts about it but never an official Microsoft page.

This is great imho, we should all be analyzing our database’s transaction log and this will help us to just that. Now there are other undocumented functions that allow us to review the log (fn_dblog and fn_dump_dblog, be careful with the last one).

So, let’s run the new command. It’s really, really simple to get details of the log for a database called TestDB: –

SELECT *
FROM sys.dm_db_log_info(DB_IB('TestDB'));
GO

I’m not going to go through what each of the columns are but the main ones that I’d initially focus on (based on my previous usage of DBCC LOGINFO) are: –

• vlf_size_mb
• vlf_active
• vlf_status

So, at a glance I can see the active part of the log and its size. We could also set up this to run on a schedule so that we could also track the internal usage of the log, pretty cool eh?

One of the other columns that’s caught my eye is: – vlf_first_lsn

DBCC LOGINFO gave us the LSN of the record that created the VLF but now we have the first LSN in the VLF which means that we can track where in the log a particular transaction was recorded (without having to use the older functions).

I like this as DBCC LOGINFO was undocumented but everyone was using it so Microsoft has now supplied an official DMF that we can use that not only replicates the information in the old command but improves upon it.

Thanks for reading!