VLF in SQL SERVER

Thanks to original blogger Shay Attiya

How are VLFs created, truncated and deleted?

Today I chose to talk about Virtual Log Files or VLF in short. VLFs are the physical files that implement the log file. The log file’s log records are stored sequentially in a set of these physical files.

Creating a VLF:

Every time space is allocated for the log file (Initial creation or log growth) new VLFs are created behind the scenes. The number of new VLFs is determined by the amount of space allocated.

1) 0MB > Space Added >= 64MB ,  4 new VLFs

2) 64MB > Space Added >= 1GB ,  8 new VLFs

3) 1GB > Space Added,                 16 new VLFs

To demonstrate, I’ll use this script to create a DB with a log file in various sizes and auto growths. The database’s data file sizes is irrelevant.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

USE     master; GO IF DB_ID('MyDB') IS NOT NULL BEGIN     DROP DATABASE MyDB; END; CREATE DATABASE MyDB ON (     NAME = MyDB,     FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLSERVERR2_SHAYMSSQLDATAMyDB.mdf',     SIZE = 10MB,     FILEGROWTH = 1MB) LOG ON (     NAME = MyDB_Log,     FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLSERVERR2_SHAYMSSQLDATAMyDB_Log.ldf',     SIZE = 10MB, -- 10MB, 250MB, 65MB, 1025MB     FILEGROWTH = 6MB); GO

To view the size and number of the VLFs created I’ll use sys.database_files and DBCC LOGINFO.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

USE     MyDB; GO SELECT     File_Name    = name ,     Size        = CAST(size*8/1024 AS VARCHAR(10))+'MB' , -- size is in pages (8KB)     Auto_Growth    = CASE is_percent_growth                     WHEN 1 THEN CAST(growth AS VARCHAR(10))+'%'                     ELSE CAST(growth*8/1024 AS VARCHAR(10))+'MB'                 END FROM sys.database_files WHERE type_desc = 'LOG'; DBCC LOGINFO; GO

10MB:

Our log file is 10MB, and as expected, we have 4 VLF files. But if we sum the size of the files we’ll get to a total of 10,477,568 Bytes while 10MB are 10,485,760 Bytes. There are 8,192 Bytes (size of a page) missing. This space is used by the file header and it does not store log records. You can see it in the StartOffset for the first VLF. This is true for all log file sizes.

Here are the results for bigger log files:

65MB:

250MB:

1025MB:

Ok, so now we know how VLFs are created, but before we continue, here’s a short overview on DBCC LOGINFO.

DBCC LOGINFO:

The fields that will be relevant to better understand the VLF behavior are:

FileId – If you have one log file, this number will be the same

FileSize – This number is in Bytes

StartOffset – This number is in Bytes

FSeqNo – The file sequence number. The VLF with the highest FSeqNo is the one where current log records are being written into. FSeqNo = 0 indicates that the file hasn’t been used yet.

Status – Two possible values: 0 – VLF is recyclable, 2 – VLF may be active (Highest FSeqNo must be active)

Parity – Two possible values: 64 and 128. Every time a VLF is reused, the parity value is switched.

CreateLSN – Indicates at what LNS (Log Sequence Number) the VLF was created. CreateLSN = 0 states that the VLF was created at the time the log was created. Files with the same CreateLSN were created at the same time.

To see how the VLFs are being written on, I’ll create some activity in the DB.

The DB is set for simple recovery mode for now.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

USE     MyDB; GO ALTER DATABASE MyDB SET RECOVERY SIMPLE; GO CREATE TABLE dbo.MyTable (Value SYSNAME); INSERT INTO dbo.MyTable SELECT TOP 1000 T1.name FROM sys.columns AS T1 CROSS JOIN sys.columns AS T2; GO 20

The empty VLF files will be filled one by one until they are all full.

Notice that the first 2 VLF statuses are 0. This means that these VLFs are recyclable and can be written on.

After generating more activity the first VLF has been overwritten and its FSeqNo changed from 22 to 26. Now the 3 other VLFs are recyclable and have status = 0.

Log Truncation:

Truncation is when inactive VLFs are marked as recyclable. This is a logical operation. Truncation occurs in the following events:

1) When a checkpoint occurs (only in auto truncate mode)

2) When the log is backed up (not in COPY_ONLY)

3) When the recovery mode is set to SIMPLE

Auto Truncate Mode:

In Auto Truncate VLFs are being overwritten once they become inactive. The log file is in this mode when the DB is in SIMPLE recovery mode, or in FULL but no backup has been made.

Adding new VLFs (Growing the Log):

In order to make the log file grow and create more VLFs we must first get out if the Auto Truncate mode. To do that, we’ll set the DB to FULL recovery mode and back it up.

1 2 3 4 5 6 7

ALTER DATABASE MyDB SET RECOVERY FULL; GO BACKUP DATABASE MyDB TO DISK = 'C:UsersShayDocumentsBackUpMyDB_BU.bak'; GO

Once we generate some more activity, all of the VLFs are full and new ones are created.

The log file grew by 6MB and 4 new VLFs are added to it. All of the files that have already been written on (FSeqNo > 0) are marked as active (Status = 2) and cannot be truncated.

Forcing Truncation:

In order to be able to recycle the VLFs, we’ll back up the Log File.

1 2 3

BACKUP LOG MyDB TO DISK = 'C:UsersShayDocumentsBackUpMyDB_LOGBU.bak'; GO

After it has been backed up, all of the VLFs are marked as inactive (Status = 0) except for the VLF that is being used at the moment. Note that the log file hasn’t shrunk, and is still 16MB.

Now new log records can be written on the truncated VLFs.

Deleting VLFs (Shrinking the Log):

If the log file is too big, it can be shrunk by removing inactive VLFs from it. To do that we’ll make the DB go into Auto Truncate mode by set the recovery mode back to SIMPLE. If we are already in SIMPLE recovery mode, we can force a truncation by using the CHECKPOINT command.

1 2 3 4 5 6

ALTER DATABASE MyDB SET RECOVERY SIMPLE; GO CHECKPOINT; GO

Now we can shrink the log file

1 2	DBCC SHRINKFILE(2,5); -- File Id, File Size in MB GO

The file has shrunk to 6MB instead of 5 like we wanted. The reason is not all of the inactive VLFs are being removed during the file shrinking. In addition, the size of a single VLF can never change.

Now What?

Now after we have a better understanding of how VLFs are created, truncated and deleted, and have also learned how to control these events, we as DBAs can modify our database for better performance by controlling the number of VFLs in the log file.

But what do we want to achieve?

First, let’s consider our options:

Many VLFs:

Having a lot of relatively small VLFs means that the log file has more flexibility during normal activity, but may cause problems during recovery and restore, and also during CHECKPOINT when in Auto Truncate mode. All of the inactive VLFs are being inspected at these events, and having a lot of then may be time consuming.

In SQL Server 2012 an error log message will appear when going over 1,000 VLFs.

Few VLFs:

Having only a few big VLFs could cause problems involving truncation. A small amount of log records can prevent the VLF from being truncated. Shrinking the log file might suffer the same problems.

Bottom Line:

There is no conclusive best Practice when it comes to determining the desired number of VLF files in the log. DBAs who have tested the subject report the “Sweet Spot” is around 100 VLFs. Like every other aspect of DB design, you should test it on your own system and configure it for what suits you best.

If you have tested it already, I’d like it if you could share your insights.