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
John G Hohengarten
November 9, 2015 at 12:01 PM
Running SQL 2012, I’m getting an error from your code copied as-is:
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
The error is actually not for Line 1 but for Line 14, the line with EXEC sp_MSforeachdb.
I think it’s related to how when normally running DBCC LOGINFO, the first column returned is RecoveryUnitId, which in most cases returns 0.
Do you have a solution to fix this?
November 9, 2015 at 12:26 PM
Good catch! I’m making a slight update now. I thought I tested it on SQL 2012… guess I was wrong. I humble apologize of the oversight.
November 9, 2015 at 12:29 PM
Let me know if the updates work, thanks for finding this.
John G Hohengarten
November 16, 2015 at 10:53 AM
Yes, I can confirm the updates work with SQL 2012.
November 17, 2015 at 6:30 AM
Thanks for your help!!
Christophe Van Olmen
November 13, 2015 at 9:53 AM
CreateLSN should be numeric(25,0).
That way you get the complete information from DBCC LOGINFO.
November 14, 2015 at 6:35 AM
I tried with a few data types, numeric gave me some issues. The whole point of this is to gather aggregated data that is summarized for multiple databases and not necessarily focusing on the details such as LSN.
Christophe Van Olmen
November 17, 2015 at 6:56 AM
I agree that the aggregated data is more interesting than the raw data, but for the sake of completeness I wanted to add the correct datatype for LSN.
Numeric gives some problems if the precision is not correct. With numeric(25, 0) you get the correct information: “LSNs are values of data type numeric(25,0). Arithmetic operations (for example, addition or subtraction) are not meaningful and must not be used with LSNs.” (https://msdn.microsoft.com/en-us/library/ms191459.aspx)
November 17, 2015 at 7:57 AM
That’s very useful information. Thank you for pitching in on this, very much appreciated.