TSQL Tuesday #11 – Misconceptions – The Tempdb Log File and VLF Counts

This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic (blog|twitter) and hosted by someone else each month. This month the host is Sankar Reddy (blog|twitter) and the topic is Misconceptions in SQL Server. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.

It is fairly common knowledge these days that proper Transaction Log management includes properly sizing the log file so that the number of VLF’s is balanced with their size for optimum performance.  If this is a foreign concept or news to you, I’d recommend that you jump over to SQLSkills and read Kimberly Tripp’s blog posts on the subject.

Transaction Log VLFs – too many or too few?

Back in July, Crys Manson (Blog|Twitter) asked about the importance of VLF count on the TempDB Log, and a conversation ensued about what the impact of tempdb recreation would be on the VLF count for the tempdb log file.

Not knowing the answer, I did some quick testing to see, and found that when the instance restarts, the number of VLF’s inside the tempdb log file is reset based on the size of the file, following the information contained in Kimberly’s blog post.  If the initial size of the tempdb log file is 1GB or larger, it is evenly divided into 16 VLF’s regardless of how large the file and resulting VLF’s might be.  To validate this, I wrote a Powershell script to test various sizes for the tempdb log, following the prescribed best practices for VLF sizing from Kimberly’s post both before and after restart.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | Out-Null

function ResetTest ([int]$size)
{
    $log.Refresh();

    if ($log.Size -gt $size)
    {
        $log.set_Size($size);
        $log.Alter();
        Restart-Service -Force -Name "MSSQLSERVER" -WarningAction SilentlyContinue |Out-Null
    }
}

function RunTest ([string]$testnumber, [int]$setsize, [int]$growsize)
{
    $log.Refresh();
    $size = $log.Size;
    while ($size -lt $setsize)
    {
        $log.set_Size(($size + $growsize));
        $log.Alter();
        $log.Refresh();
        $size = $log.Size;
    }
    
    $vlfbefore = $tempdb.ExecuteWithResults("DBCC LOGINFO").Tables[0].Rows.Count
    
    Restart-Service -Force -Name "MSSQLSERVER" -WarningAction SilentlyContinue | Out-Null
    
    Start-Sleep -Seconds 60 #Wait for Instance Recovery to Complete 
    
    $tempdb.Refresh();

    $vlfafter = $tempdb.ExecuteWithResults("DBCC LOGINFO").Tables[0].Rows.Count
        
    $results = New-Object PSObject -Property @{            
        TestNumber                = $testnumber                 
        LogFileSize               = $log.Size              
        VLFBeforeRestart         = $vlfbefore
        VLFAfterRestart          = $vlfafter}    

    $results | select TestNumber, LogFileSize, VLFBeforeRestart, VLFAfterRestart
}
        
cls

$smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "(local)"
$tempdb = $smosvr.Databases['tempdb']
$log = $tempdb.LogFiles.Item(0);

ResetTest 8387584
RunTest "16GB Log" 16775168 8387584
ResetTest 8387584
RunTest "24GB Log" 25162752 8387584
ResetTest 8387584
RunTest "32GB Log" 33550336 8387584

Regardless of how you manually grew the log file to set appropriate VLF sizes, at server restart the log is always reset to 16 VLF’s of equal size, which is not necessarily ideal according to additional discussion that occurred on Twitter.  However, there is not much that you can do about this, aside from setting the log file to 8GB for its initial size and then setting a reasonable size for Autogrowth so that it can grow if necessary, and still minimize the VLF count which once again may not be ideal for all environments.