Visualizing Log File VLF Sizing
One of my favorite queries this week is the following query that creates a text based bar chart to quickly help visualize the VLF files on any database log file. In the last month I have given the “TempDB Do This and Don’t Do That” presentation twice, once at the Bellingham SQL Server users group, and another time at SQL Saturday Redmond. One of the questions that came up after the users group meeting was around an easy way to visualize the Virtual Log Files. Its one thing to just get a count, but to see the sizing of each VLF is helpful to understand how VLFs work.
If you are not familiar with VLFs, these are Virtual Log Files, or the chunks that make up your entire log file. When your log file grows, VLFs are added to help manage the file space. For the log growth, between 4 and 16 VLFs are added for each growth of the log file (except on SQL Server 2014 and newer, which modifies the sizing algorithm a bit).
The following query uses DBCC LOGINFO with the output dumped into a table variable so that the results can be used in the final query.
use tempdb;
go
DECLARE @logInfoResults AS TABLE
(
[RecoveryUnitId] BIGINT, -- only on SQL Server 2012 and newer
[FileId] TINYINT,
[FileSize] BIGINT,
[StartOffset] BIGINT,
[FSeqNo] INTEGER,
[Status] TINYINT,
[Parity] TINYINT,
[CreateLSN] NUMERIC(38,0)
);
INSERT INTO @logInfoResults
EXEC sp_executesql N'DBCC LOGINFO WITH NO_INFOMSGS';
SELECT cast(FileSize / 1024.0 / 1024 AS DECIMAL(20,1)) as FileSizeInMB,
[Status] ,
REPLICATE('x', FileSize / 1024 / 1024 ) as [BarChart ________________________________________________________________________________________________]
FROM @logInfoResults ;
Note: the [RecoveryUnitID] column in the table variable should be removed on SQL Server versions older than 2012, as that column was added to DBCC LOGINFO in SQL Server 2012. The [RecoveryUnitID] column appears to have been accidentally added by Microsoft, and although the column appears there, it is never used for anything.
When I switch the output in SSMS to “Results to Text” instead of “Results to Grid” you can easily see what your VLF sizing looks like.
Each x in the bar chart column represents 1MB of VLF size. Here is another example showing 16 VLF files all sized at about 125MB.
You can run this query on your TempDB, and other busy databases to find out what your VLF file layout looks like.
Here is an updated script that has adds another column called “TextStatus” to the output to give you a better idea of what the different statuses mean. Note the same rules apply on the [RecoveryUnitId] as above that this should be removed from the query on versions of SQL Server Older than 2012.
DECLARE @logInfoResults AS TABLE
(
[RecoveryUnitId] BIGINT, -- only on SQL Server 2012 and newer[FileId] TINYINT,
[FileSize] BIGINT,
[StartOffset] BIGINT,
[FSeqNo] INTEGER,
[Status] TINYINT,
[Parity] TINYINT,
[CreateLSN] NUMERIC(38,0)
);
INSERT INTO @logInfoResults
EXEC sp_executesql N'DBCC LOGINFO WITH NO_INFOMSGS';
SELECT cast(FileSize / 1024.0 / 1024 AS DECIMAL(20,1)) as FileSizeInMB,
case when FSeqNo = 0 then 'Available - Never Used' else (Case when status = 2 then 'In Use' else 'Available' end) end as TextStatus,
[Status] ,
REPLICATE('x', FileSize / MIN(FileSize) over()) as [BarChart ________________________________________________________________________________________________]
FROM @logInfoResults ;
-Steve Stedman
Related Links:
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply