Understand Virtual Log Files in SQL Server Transaction Log File

Dung DinhData Platform Consultant
CERTIFIED EXPERT
Published:
Updated:

What are Virtual Log Files?


The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files.

The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.
 

How many VLFs are in a SQL Transaction Log File?


Actually, we don’t have fixed number of virtual log files for a physical log file and they are decided by SQL Database Engine. SQL Database Engine often bases the size on the initial information it gets when we create a database to make determine the number of VLFs or bases it on the extended size of transaction log file.

The only time virtual log files affect system performance is if the log files are defined by small size and growth_increment values. If these log files grow to a large size because of many small increments, they will have lots of virtual log files. This can slow down database start-up and also log backup and restore operations. We recommend that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value.
 

Detect VLFs in SQL Transaction Log File


Use DBCC LOGINFO as a DBA command to detect how many VLFs in SQL transaction log file. For example:

USE AdventureWorks2012
                      go
                      DBCC LOGINFO

Open in new window

The result shows that there are 387 VLFs in the AdventureWork log file. The output of DBCC Loginfo contains the following columns:

  • FileId : represents log file identifier
  • FileSize: Size of Virtual Log File
  • StartOffset: beginning of VLF in terms of bytes
  • FSeqNo: VLF Sequence number
  • Status: 0 represent inactive, 2 represents active
  • Parity: parity information,value may be 0 or 64 or 128
  • CreateLSN: Log Sequence No. from which VLF begins.
dbcc-loginfo.png
Case 1: Number of VLFs is 4 when the size of transaction log file is less than or equals 64MB
 
USE master
                      GO
                      CREATE DATABASE Database_Log64MB ON PRIMARY
                      ( NAME = N’Database_Log64MB’,
                      FILENAME = N’D:\DataSQL2012\Database_Log64MB.mdf’ ,
                      SIZE = 4096KB ,
                      FILEGROWTH = 1024KB )
                      LOG ON
                      ( NAME = N’Database_Log64MB_log’,
                      FILENAME = N’D:\DataSQL2012\Database_Log64MB_log.ldf’ ,
                      SIZE = 1024KB ,
                      FILEGROWTH = 10%)
                      GO
                      USE Database_Log64MB
                      GO
                      DBCC LOGINFO

Open in new window

dbcc-loginfo-1.pngCase 2: Number of VLFs is 8 when the size of transaction log file is greater than 64MB and less than or equals 1GB



USE master
                      Go
                      CREATE DATABASE Database_Log1GB ON PRIMARY
                      ( NAME = N’Database_Log1GB’,
                      FILENAME = N’D:\DataSQL2012\Database_Log1GB.mdf’ ,
                      SIZE = 4096KB ,
                      FILEGROWTH = 1024KB )
                      LOG ON
                      ( NAME = N’Database_Log1GB_log’,
                      FILENAME = N’D:\DataSQL2012\Database_Log1GB_log.ldf’ ,
                      SIZE = 102400KB ,
                      FILEGROWTH = 10%)
                      GO
                      USE Database_Log1GB
                      GO
                      DBCC LOGINFO

Open in new window

dbcc-loginfo-2.pngCase 3: Number of VLFs is 16 when the size of transaction log file is greater than 1GB



USE master
                      Go
                      CREATE DATABASE Database_LogMore1GB ON PRIMARY
                      ( NAME = N’Database_LogMore1GB’,
                      FILENAME = N’D:\DataSQL2012\Database_LogMore1GB.mdf’ ,
                      SIZE = 4096KB ,
                      FILEGROWTH = 1024KB )
                      LOG ON
                      ( NAME = N’Database_LogMore1GB_log’,
                      FILENAME = N’D:\DataSQL2012\Database_LogMore1GB_log.ldf’ ,
                      SIZE = 1049600KB ,
                      FILEGROWTH = 10%)
                      GO
                      USE Database_LogMore1GB 
                      GO
                      DBCC LOGINFO

Open in new window

dbcc-loginfo-3.png As you can see, the number of VLFs in the SQL Transaction Log file depends on initial size of Log File or amount of extended size while extending SQL Transaction Log File. If we go back to database [i]Database_Log64MB[]/i] and extend the size of log file to larger than 64MB then we can see number of VLFs changes to 12.

dbcc-loginfo-4.png

Performance issue with large number of VLFs

Many questions we see ask why large number of VLFs can impact performance of SQL Database Engine. It can slow down database start-up and also log backup and restore operations. Ideally, there are 15-50 VLFs for SQL Transaction Log.

How to determine size of SQL Transaction Log File and reduce number of VLFs?


A large number of VLFs cause performance issue and we have to determine size of SQL Transaction Log and reduce number of VLFs.

  1. First we use DBCC LOGINFO to detect the size of the SQL Transaction Log and then determine appropriate size.
  2. Second, we need to backup the transaction log.
  3. Third, we need to shrink log file. Finally, modify size of transaction log to appropriate size.
2
2,210 Views
Dung DinhData Platform Consultant
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.