Visualizing VLF’s – Updated

Download PDF

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.

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


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 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 accidently added by Microsoft, and although the column appears there, it is never used for anything.

The output will look something like this:

vlfvisualization

 

-Steve Stedman

 

Related Links:

 

Have you tried Database Health Monitor to give your SQL Server a checkup.

Star_of_life

Tagged with: , , , , , , ,
4 comments on “Visualizing VLF’s – Updated
  1. vfrank66 says:

    Must have appended the FileId to the comment line. Needs to be a newline
    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)
    );

  2. Joseph Hayes says:

    Thanks, Steve. This is very useful. Much obliged :)

  3. Mel Vargas says:

    Thanks, Steve.

    Something I noticed is that if I run the script on a restored copy of the database it will show all the “Availiable” files as “Available – Never Used”.

    As with all scripts, I ran it in a test environment and basically freaked out at first when I saw that all the files except for one had this status.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.