Virtual Log Files (VLFs)

What is VLFs?

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 in 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 on initial information when we create a database to make decision number of VLFs or bases on amount of 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

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

USE AdventureWorks2012
go
DBCC LOGINFO

DBCC LOGINFO

 

The result shows that there are 387 VLFs of AdventureWork log file.

The output of DBCC LOGINFO contains following columns:

1-  FileId : re-present log file identifier

2-  FileSize: Size of  Virtual Log File

3-  StartOffset: beginning of VLF in terms of bytes

4-  FSeqNo: VLF Sequence number

5-  Status: 0 represent inactive, 2 represents active

6-  Parity: parity information,value may be 0 or 64 or 128

7-  CreateLSN: Log Sequence No. from which VLF begins.

Now, we will check how many VLFs are created when we create a database

Case 1: Number of VLF files are 4 when Transaction Log Size is less than or equals to 64 MB

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

DBCC LOGINFO -1

Case 2: Number of VLF files are 8 when Transaction Log Size is greater than 64 MB and less than or equals to 1 GB

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

DBCC LOGINFO -2

Case 3: Number of VLF files are 16 when Transaction Log Size is greater than 1 GB

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%)

DBCC LOGINFO -3

 

As you know, number of VLFs in SQL Transaction Log file depends on initial size of Log File or amount of extended size while extending SQL Transaction Log File. We’re coming back to database Database_Log64MB and extend size of log file larger 64MB then we can see number of VLFs changes to 12

DBCC LOGINFO -4

 

 

Performance issue with large number of VLFs

Many questions we’re facing 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.

The first step, we use DBCC LOGINFO to detect how much used size of SQL Transaction Log and then determine appropriate size.

The second, we need to backup the transaction log.

The third, we need to shrink log file.

Finally, modify size of transaction log to appropriate size.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s