SQL-Server: What are VLF’s and why should I care about them?

Introduction: What are VLF’s?

In SQL-Server, the transaction log is divided into several logical units called virtual log files (VLF). The amount of virtual log files and their size are managed automatically by the database engine. The database engine adds more VLF’s to the transaction log file every time when the transaction log file grows. For this reason, admins can indirectly affect the amount of VLF’s through the auto-growth setting of the database transaction log file. For more information regarding VLF’s and the transaction log architecture, check out this link from Microsoft.

Why should these VLF’s now deserve our attention?

The answer is quite simple: Too many virtual log files can slow down the recovery time of a database and the transaction log backups. They can even affect features that depend on the transaction log, like Change data capture, for example.

The slowdown is caused by the many VLF’s that SQL Server has to handle during the recovery process. Too many VLF’s are usually caused by too small auto growth configurations of the transaction log file, which results in many small incremental growths and, finally, many VLF’s.

To see all the problems many VLF’s can cause and how SQL-Server systematically decides how many VLF’s should be created during a growth – Check out the link above.

How can we reduce the amount of VLF’s?

So, we know now that too many VLF’s can cause undesirable results. But how can we solve the issue? A proactive approach would be to configure the initial size of the log file appropriately to the expected size so that auto growth can be prevented. Or to increase the auto growth size to prevent too many incremental growths. Please don’t forget to regularly schedule transaction log backups: They are necessary for truncating the transaction log in the full and bulked log recovery model, which is essential for freeing and reusing the place in the transaction log file. A good way to check the blockers for the re-usage of the transaction log file place is the column “log_reuse_wait_desc” from the “sys.databases” view in the master system database – But that would go out of scope from this blog post.

But what if we are already in this situation? – So then we must shrink the transaction log file of the database and regrowth the file again. SQL-Server will then reset the amount of virtual log files to a more reasonable amount. I wrote the following Power-Shell script to check the amount of virtual log files and reduce the total amount through the following steps:

1. Perform manual checkpoint
2. Backup the transaction log
3. Shrink the transaction log file
4. Regrowth the transaction log file to the previous size

When using the script, please verify that you have installed the Power-Shell module “dbatools”. Please also note the following parameters:

$Instance: Specify here the instance where you want to check the virtual log files. Make sure that you have appropriate rights on the instance.

$DBs2Check: Specify the list of databases for which the virtual log files should be checked.

$VLFTS: Specify the threshold of maximum accepted virtual log files. 1000 virtual log files may not cause problems, but it’s a proactive approach.

$Backupdir: In case a database has exceeded the defined threshold, the transaction log will be shrunk. For that, a previous transaction log backup will be performed. Define here the backup directory. If it’s empty, the default backup director of the instance will be used.

$Shrink: When this parameter is set to 1, and a database has exceeded the defined threshold of virtual log files, the actions above will be performed or scripted out to reduce the amount of VLF’s.

If the parameter is set to 0, the script would only check the number of VLF’s and write it to the console (note: Shrinking and growing the transaction log file causes blocking and high I/O contention – even when Instant File Initialization is configured. So shrinking and re-growing is not appropriate for running production)

$PrintOnly: Set this parameter to 1 in combination with the $Shrink parameter to only print out the T-SQL commands, which would be executed on the instances, instead of running them. (Note: If one of the T-SQL commands executed against the instance should fail, the Power-Shell script will also fail. I highly recommend to use this parameter to run the printed T-SQL commands separately on the instance to have a better understanding of potential errors)

#################################################################################
##Parameters:

$Instance = ''
$DBs2Check = ('')
$VFLTS = 1000

$Backupdir = ''
$Shrink = 0
$PrintOnly = 0

#################################################################################

if ($backupdir -eq '')
{
$SQLOut1 = Invoke-Sqlcmd -ServerInstance $Instance -Query "Select SERVERPROPERTY('InstanceDefaultBackupPath')"
$Backupdir = $SQLOut1[0] + '\'
}

foreach ($database in $DBs2Check)
    {

    $VFL = Get-DbaDbVirtualLogFile -SqlInstance $Instance | where-object Database -eq "$($database)"
    
    if ($VFL.count -gt $VFLTS -and($Shrink -eq 0))
    {
    Write-Host "Database $($database) has $($VFL.count) virtual log files. - Shrink transaction log is disabled." -ForegroundColor Red
    } 

    if ($VFL.count -gt $VFLTS -and($Shrink -eq 1))
    {
    Write-Host "Database $($database) has $($VFL.count) virtual log files. - Shrink parameter is enabled" -ForegroundColor Red

    ##Perform database checkpoint
    $SQL1 = "USE $($database); checkpoint;"

        if($PrintOnly -eq 1)
        {
        WRITE-HOST $SQL1
        }

        if($PrintOnly -eq 0)
        {
        Invoke-Sqlcmd -ServerInstance $Instance -Query $SQL1
        }

    ##Backup trn log
    $Date = Get-Date
    $Datestring = $Date.ToString("yyyyMMddHHmmss")
    $backuppath = $Backupdir + $database + '_' + $Datestring + '.trn'

    $SQL2 = "USE $($database); BACKUP LOG $($database) TO DISK = '$($backuppath)';"

        if($PrintOnly -eq 1)
        {
        Write-Host $SQL2
        }

        if($PrintOnly -eq 0)
        {
        Invoke-Sqlcmd -ServerInstance $Instance -Query $SQL2
        }

    ##Read out Log file size
    $SQLOut2 = Invoke-Sqlcmd -ServerInstance $Instance -Query "SELECT Name FROM sys.master_files WHERE type_desc = 'LOG' and database_id = DB_ID('$($database)')"
    $SQLOut3 = Invoke-Sqlcmd -ServerInstance $Instance -Query "Select Size / 128 FROM sys.master_files WHERE type_desc = 'LOG' and database_id = DB_ID('$($database)')"

    $Log_Name = $SQLOut2[0]
    $Log_Size = $SQLOut3[0]


    ##Shrink Log file
    $SQL3 = "USE $($database); DBCC SHRINKFILE ($($Log_Name));"

        if($PrintOnly -eq 1)
        {
        Write-Host $SQL3
        }

        if($PrintOnly -eq 0)
        {
        Invoke-Sqlcmd -ServerInstance $Instance -Query $SQL3
        }

    ##Regrowth file
    $FileSizeKB = $Log_Size * 1024
    $SQL4 = "USE $($database); ALTER DATABASE $($database) MODIFY FILE ( NAME = '$($Log_Name)', SIZE = $($FileSizeKB)KB)"
        if($PrintOnly -eq 1)
        {
        Write-Host $SQL4
        }

        if($PrintOnly -eq 0)
        {
        Invoke-Sqlcmd -ServerInstance $Instance -Query $SQL4
        $VFLnow = Get-DbaDbVirtualLogFile -SqlInstance $Instance | where-object Database -eq "$($database)"
        Write-Host "Database $($database) has $($VFLnow.count) virtual log files. - shrink and regrowth proceeded" -ForegroundColor Yellow
        }
    }

    if ($VFL.count -lt $VFLTS)
    { 
    Write-Host "Database $($database) has $($VFL.count) virtual log files." -ForegroundColor Green
    }
    }  

Let’s get practical:

For Demo purposes, I’ve created 2x databases. For the first database, I have set the autogrowth setting for the transaction log file, and the initial file size to 1 MB. This is terrible, I know. But I want to force SQL-Server to perform many small incremental growths to demonstrate the effect. For the other database, the default configuration of 64MB is in place.

Now, I’m using parts of the script from my previous blog (check it out – it’s very interesting 😉) to generate some data and, therefore, also some activities for the transaction log. After 15 million rows are inserted, we can proceed and compare the amount of VLF’s between both databases:

For checking the amount of VLF’s in both databases, I’m using the Power-Shell script above:

For that, I’m putting the instance Name and the two databases I want to check into the parameters at the top, and I set the Shrink Parameter to 0 as I only want to check the total amount of VLF’s first.

After executing the script, we can see at the bottom that the Database TestVLF01, with the bad transaction log size and auto-growth configuration, has 6259 virtual log files, while the other database has only 100. This is the result of many incremental growths of the transaction log file, which we forced SQL-Server to perform through the inappropriate configuration.

When I change the $Shrink and $PrintOnly Parameter and execute the script again, the script will write into the output all T-SQL commands, which I would have to run to reduce the amount of VLF’s for the Database TestVLF01:

I now use these printed-out commands to reduce the amount of VLF’s. For that, I run them one by one from the management studio:

After running the generated T-SQL Statements above, I will check the amount of virtual log files again with the Power-Shell script:

The number of virtual log files has now remarkably decreased to 20.

Please note: It depends, where the active virtual log files is located within the transaction log file. When the active VLF is at the end of the transaction log file, you will be unable to shrink. If that is the case, you must make sure, the transaction log file is truncated and wait until the logical log has wrapped up to the beginning of the transaction log file.

Conclusion

A lot of VLF’s can cause bad results. It makes sense to be aware of that and to check them sometimes. Please also remember to configure the Transaction Log file appropriately regarding initial size and auto-growth to prevent too many incremental growths, which are usually the root cause of too many VLF’s. Therefore, do not forget to check this configuration after reducing the amount of VLF’s through the process described in this blog to make sure that it is not only fighting against symptoms but also against the root cause 😉