USE AdventureWorks2012
go
DBCC LOGINFO
The result shows that there are 387 VLFs in the AdventureWork log file. The output of DBCC Loginfo contains the following columns:
USE master
GO
CREATE DATABASE Database_Log64MB ON PRIMARY
( NAME = N’Database_Log64MB’,
FILENAME = N’D:\DataSQL2012\Database_Log64MB.mdf’ ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N’Database_Log64MB_log’,
FILENAME = N’D:\DataSQL2012\Database_Log64MB_log.ldf’ ,
SIZE = 1024KB ,
FILEGROWTH = 10%)
GO
USE Database_Log64MB
GO
DBCC LOGINFO
Case 2: Number of VLFs is 8 when the size of transaction log file is greater than 64MB and less than or equals 1GB
USE master
Go
CREATE DATABASE Database_Log1GB ON PRIMARY
( NAME = N’Database_Log1GB’,
FILENAME = N’D:\DataSQL2012\Database_Log1GB.mdf’ ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N’Database_Log1GB_log’,
FILENAME = N’D:\DataSQL2012\Database_Log1GB_log.ldf’ ,
SIZE = 102400KB ,
FILEGROWTH = 10%)
GO
USE Database_Log1GB
GO
DBCC LOGINFO
Case 3: Number of VLFs is 16 when the size of transaction log file is greater than 1GB
USE master
Go
CREATE DATABASE Database_LogMore1GB ON PRIMARY
( NAME = N’Database_LogMore1GB’,
FILENAME = N’D:\DataSQL2012\Database_LogMore1GB.mdf’ ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N’Database_LogMore1GB_log’,
FILENAME = N’D:\DataSQL2012\Database_LogMore1GB_log.ldf’ ,
SIZE = 1049600KB ,
FILEGROWTH = 10%)
GO
USE Database_LogMore1GB
GO
DBCC LOGINFO
As you can see, the number of VLFs in the SQL Transaction Log file depends on initial size of Log File or amount of extended size while extending SQL Transaction Log File. If we go back to database [i]Database_Log64MB[]/i] and extend the size of log file to larger than 64MB then we can see number of VLFs changes to 12.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)