Visualizing VLFs on SQL Server

This is an update to Visualizing VLFs – A while back a wrote a blog post with a query to Visualize the VLF’s in your database. Today I have an update to that script.

The previously updated script added another column called “TextStatus” to the output to give you a better idea of what the different statuses mean. You now get 3 statuses shown, “In Use”, “Available”, and “Available Never Used”. The If you have lots of VLFs that are “Available Never Used” that may be an indication that your log file may be larger than you need. If you don’t have any that are “Available Never Used” the log may be smaller than you need.

This latest update changed the character shown in the bar chart to a X or an O depending on if the current file is in use.

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
    EXECUTE sp_executesql N'DBCC LOGINFO WITH NO_INFOMSGS';
  
SELECT 
       DB_NAME(), 
       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( CASE WHEN [Status] = 2 THEN 'X' ELSE 'O' END , FileSize / MIN(FileSize) OVER()) 
             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.

The output of my visualizing VLFs query will look something like this:

(note added later) As far as blog posts that I have written over the years, this visualizing vlfs post is perhaps one of the most common that I come back to regularly to copy and run the script on SQL Server. I hope you find as much value in this as I do.

-Steve Stedman

Visualizing VLFs – Another update

Related Links:

Leave a Reply Cancel reply