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.
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