How to check your Transaction Log VLF Count in SQL Server

You can probably throw a stone and hit an article about properly maintaining the transaction log.

In fact here are my favorites:

That last one is really important by the way, the fundamental change to how a VLF is added to the log file in 2014 is important to know. ( Especially if you're upgrading from pre-2014 to 2014 or higher. Since it means you have to reconsider the log growth on your databases. )

Here is a quick breakdown:


2014 and Newer:

The problem I ran into was finding the actual VLF count in an easy to read, and accessible place. There is plenty of good information about there that will tell you HOW to plan your VLFs for a database, so I wont be covering that here.

This is where DBCC LOGINFO('$(DatabaseName)') comes in. This sweet, undocumented command will return one row per VLF in the log file. It also returns quite a bit more information.

Read about that here. It's a great breakdown of the command.

The problem I encountered was running it for each database as it quickly became a chore. It would be nice if there was a way to get that data without having to run it on each DB. Also to look into some details about the log file, like what the growth is set to? My scipting senses were tingling.

Take a look at the output from DBCC LOGINFO:

You can see it does offer quite a bit in terms of information about the individual VLF files.

And here is the output from my script, you can find it here:

Column RecoveryUnitId is not in every version and it's on my list of things to clear up.

Notice that it handles Multiple Log files easily. Now you can get a great look at the state of your logfiles with one easy script. (Next week, I'll show you how to run it directly from Powershell)