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;
$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
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
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