• Madeira Team

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 (

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.

Transact-SQL

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

USE

MyDB;

GO