The size and number of VLF added at the time of expanding the transaction log is based on this following criteria:
- transaction log size less than 64MB and up to 64MB = 4 VLFs
- transaction log size larger than 64MB and up to 1GB = 8 VLFs
- transaction size log larger than 1GB = 16 VLFs
1. CREATE A DATABASE WITH LOG FILE LESS THAN 64 MB THAT WILL CREATE 4 VLFS
–Note that the transaction log is 1 megabyte in size, and the autogrowth is set to grow in increments of 10% (bad practice)
CREATE DATABASE [Log_Growth]
ON PRIMARY
( NAME = N’Log_Growth’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATALog_Growth.mdf’ ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N’Log_Growth_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATALog_Growth_log.ldf’ ,
SIZE = 1024KB , –1 megabyte
FILEGROWTH = 10%)
GO
USE [Log_Growth]
GO
–Each row indicates a VLF
DBCC LOGINFO
GO
–4 VLFS
–look at the size of the database and note transaction log is 1 MB and data file is 4MB