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.

Transact-SQL

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

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 (