Get valuable Virtual Log File (VLF) information for all your databases log files

I had this problem where I needed to gather Transaction Log information on multiple databases and check for valuable statistics on them. Running the command [DBCC Loginfo] brings back a number of rows for each Virtual Log File (VLF) in your Log File. It is really hard to do anything useful with that information on a larger scale. Each row returned gives you an estimate for the number of VLFs per Log File. Sure you could use the internal (and hidden) system stored procedure sp_msforeachdb to get the information for all database but it looks horrible. Here try it out for yourself before you read the rest of the post:

exec sp_MSforeachdb 'Use [?] select db_name(); DBCC LogInfo'

So why not make it better? That’s what I thought to myself, and I have recently been playing with storing DBCC command output to tables for analysis. I’ve put some together some code that allows you to capture the output of DBCC LogInfo into a Temp Table and then get some interesting information about the number of VLFs per database and other valuable information; see the comments for more information. Just by storing some of this data temporarily, I was able to write queries against it and discovered a major inconsistency in the size of my VLFs in a Log File that could potentially cause performance issues.

Feel free to create a permanent table for this data and run it on a regular basis to get an understanding of what your system is doing for troubleshooting. I also commented out the date field since I deemed it unnecessary, but if you’re looking for trending it maybe a good option to have that additional data.

For a quick refresher on T-Log Files and VLFs

If you don’t remember what VLFs are or how they affect performance, please see my SQL Snack below for a refresher.

The Code

create table #VLFCounts
(
[Database Name] varchar(100) default db_name(),
--RecoveryUnitID int, --Uncomment and use for SQL 2012+
FileId smallint,
FileSize bigint,
StartOffSet bigint,
FSeqNo int,
Status tinyint,
Parity smallint,
CreateLSN float,
--ExecutionDate datetime default getdate()
)

--Use this code for SQL Servers prior to SQL 2012
exec sp_MSforeachdb 'Use [?] insert into #VLFCounts (FileID,FileSize ,StartOffSet ,FSeqNo,Status,Parity,createLSN) Exec(''dbcc loginfo'')'

-- Use this code instead for SQL 2012+
/*
exec sp_MSforeachdb 'Use [?] insert into #VLFCounts (RecoveryUnitID,FileID,FileSize ,StartOffSet ,FSeqNo,Status,Parity,createLSN) Exec(''dbcc loginfo'')'
 */
--- raw data
select *
from #VLFCounts
order by [Database Name]

--Count of VLFs per database and total size of Log
--Average VLF size is tricky, because of the way that the log file grows
--you can have mixed sizes in VLFs; this is especially true if you use % growth

select [Database Name], count(status) as VLFCount, sum(filesize)/(1024*1024) as SizeInMb,
(sum(filesize)/(1024*1024))/(count(status)) as AvgVLFSizeInMb
from #VLFCounts
group by [Database Name]
order by [Database Name]

--Count of VLFs and sum Size by Status (Used vs not Used)

select [Database Name], case when status = 0 then 'Available' else 'Used' end as StatusDesc, count(status) as VLFCount,
sum(filesize)/(1024*1024) as SizeInMb
from #VLFCounts
group by [Database Name], status
order by [Database Name]

drop table #VLFCounts