• 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

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.


VLFs 10MB

Here are the results for bigger log files:

65MB:


VLFs 65MB

250MB:


VLFs 250MB

1025MB:


VLFs 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.

Transact-SQL

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

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.


VLFs FullLog

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.


VLFs FullLog2

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.

Transact-SQL

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

1

2

3

4

5

6

7