Audit VLFs on your SQL Server

I’ve been reading a bit about VLFs (Virtual Log Files) this week. I’ve found quite a few interesting links, especially this one, informing us that there’s such a thing as too few or too many VLFs.

We can view details about VLFs using the DBCC LOGINFO TSQL command. This only works against the current database context but I decided to make this process less tedious, and human-readable, so I knocked up a quick script;

-- Create a temp table to hold log info
CREATE TABLE #tmp_log_info
(
      FileId INTEGER,
      FileSize BIGINT,
      StartOffSet BIGINT,
      [Status] INTEGER,
      FSeqNo INTEGER,
      Parity SMALLINT,
      CreateLSN NUMERIC(38,0)
);
 
-- Same as above but with database name
-- Can insert in one statement
CREATE TABLE #log_info
(
      FileId INTEGER,
      FileSize BIGINT,
      StartOffSet BIGINT,
      [Status] INTEGER,
      FSeqNo INTEGER,
      Parity SMALLINT,
      CreateLSN NUMERIC(38,0),
      [Database] VARCHAR(128)
);
 
EXEC sp_MsForEachDb 'INSERT INTO #tmp_log_info EXEC(''DBCC LOGINFO(?)'');
                              INSERT INTO #log_info
                               SELECT *, ''?''
                              FROM #tmp_log_info;
                              TRUNCATE TABLE #tmp_log_info;';
 
SELECT [Database],
            COUNT(*) AS Vlf_count,
            CAST(MAX(FileSize) AS FLOAT) / 1048576  AS biggest_vlf_MB,
            CAST(MIN(FileSize) AS FLOAT) / 1048576 AS smallest_vlf_MB
FROM #log_info
GROUP BY [Database]
ORDER BY COUNT(*) DESC;
 
 
-- Clean up
DROP TABLE #tmp_log_info;
DROP TABLE #log_info;

This will display a summary report for all databases on the instance of SQL Server.

Database – The database the log file belongs to.

Vlf_count – The total number of VLFs.

biggest_vlf_MB – The biggest virtual log file in MB.

smallest_vlf_MB – The smallest virtual log file in MB.

Database	Vlf_count	biggest_vlf_MB	smallest_vlf_MB
msdb		26		0.4453125	0.2421875
master		5		0.25		0.2421875
model		3		0.25		0.2421875
tempdb		2		0.25		0.2421875
ft_test		2		0.3125		0.2421875

UPDATE: Just found a very similar post here.

UPDATE: Thought I’d try to bring something to the table and come up with a pure-powershell way to do this but it doesn’t seem that the VLF information is exposed;

?View Code POWERSHELL
$server = "localhost";
# Load SMO 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; 
# create server objects 
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server $server;
$db = $srv.Databases["tempdb"];
 
foreach($log in $db.LogFiles)
{
	Write-Host $log.Name;
	Write-Host "=================";
	# Output each property
	foreach($p in $log.Properties)
	{
		Write-Host $p.Name;
	}
}

Exposed logfile properties…

templog
=================
FileName
Growth
GrowthType
ID
MaxSize
Size
UsedSpace
BytesReadFromDisk
BytesWrittenToDisk
IsOffline
IsReadOnly
IsReadOnlyMedia
IsSparse
NumberOfDiskReads
NumberOfDiskWrites
PolicyHealthState
VolumeFreeSpace

If anyone knows a way I’d be interested to hear. Cheers


3 Comments

  1. Stephen Mills says:

    Here’s a PowerShell Example that should work for you. The nice thing in powershell you can easily extend it to multiple servers. In this case if you put this in a function you could call it with different servers and audit all of your systems.

    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null
    # create server objects
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null
    $Srv = New-Object Microsoft.SqlServer.Management.SMO.Server $Server

    $Srv.Databases |
    foreach {
    $Measure = $_.ExecuteWithResults(‘dbcc loginfo’).Tables[0] | Measure-Object -Minimum -Maximum -Average FileSize

    New-Object psobject -Propert @{
    ‘Server’=$Server;
    ‘Database’=$_.Name;
    ‘LogSegments’=$Measure.Count;
    ‘MaxVLFMB’=$Measure.Maximum/1mb;
    ‘MinVLFMB’=$Measure.Minimum/1mb;
    ‘AverageVLFMB’=$Measure.Average/1MB
    } | select Server, Database, LogSegments, MaxVLFMB, MinVLFMB, AverageVLFMB
    } | Format-Table * -AutoSize

  2. Rhys says:

    Hi Stephen,

    Thanks for sharing. Was king of hoping for a pure powershell solution but I guess I’m just being too fussy!

    Cheers,

    Rhys

  3. […] are several posts about this and doing this with PowerShell like this one or this one. As is my wont I chose to output to Excel and colour code the cells depending on the number of VLFs […]

Leave a Reply