SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Update to DBCC LOGINFO in SQL Server 2017

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!


The DBA Who Came In From The Cold

I’m Andrew Pruski and I am a SQL Server DBA with 5 years experience in database development and administration.

The online SQL Server community has helped me out immensely throughout my career, whether from providing reference material in blog posts, or answering my (sometimes obscure) questions on forums. So, to try and say thank you, I would like to contribute my own experiences in the hope that they could benefit someone out there.

So here’s my general ramblings and thoughts about working as a SQL Server DBA.

You can find me on twitter @DBAFromTheCold

If you have any feedback on my blog please send me an email to dbafromthecold@gmail.com.

Comments

Leave a comment on the original post [dbafromthecold.com, opens in a new window]

Loading comments...