SQL Server Database Transaction Log Files – Virtual Log Files: Circular Behavior: Part 2

Hi Friends,

In my previous blog I just mention about what will be the number of Virtual Log Files at the time of creation of new database, where number of VLF files depends on the initial size of SQL Server database transaction log files. As the size of transaction log increases then number of VLF Files will also increase, where number of VLF Files will depends on the Transaction Log file growth configuration. You can Read the previous blog from here;

Today , I am going to focus on how Transaction Log maintain circular behavior.

Lets create a new database with the following code:

CREATE DATABASE [SQLGEEKS] ON  PRIMARY
( NAME = N'SQLGEEKS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\SQLGEEKS.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'SQLGEEKS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\SQLGEEKS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Now just run the DBCC LOGINFO to chcek the current number of VLFs.

Now just create a new table and insert some rows

create table xttest1
(
    id int,
    name char(8000)
)
Go
insert into xttest1 values(1,'prince')
go 40
DBCC LOGINFO

Now the output of DBCC LOGINFO is:

Here when first VLF (FSeqNo 33) filled completely then next VLF (FSeqNo 34) starts to fill up, as shown above. The  second VLF Status change from 0 to 2 i.e. inactive to active and Parity of second VLF also change from 0 to 64. Now again run the below code to insert the data and again check the DBCC LOGINFO output.

insert into xttest1 values(1,'prince')
go 40
DBCC LOGINFO

Now the output of DBCC LOGINFO is:

Second VLF was already filled so transaction filling start from third VLF (FSeqNo 35) when third VLF (FSeqNo 35) filled completely then next VLF (FSeqNo 36) starts to fill up, as shown above. The  third and fourth VLF Status change from 0 to 2 i.e. inactive to active and Parity of third and fourth VLF also change from 0 to 64 i.e. never used to used. The one more thing to be notice here is the status of first two VLFs change from 2 to 0 i.e. active to inactive means there is no active transaction in both these VLFs and you can use these VLFs again.  Now again run the below code to insert the data and again check the DBCC LOGINFO output.

insert into xttest1 values(1,'prince')
go 40
DBCC LOGINFO

Previouly third VLF was filled completely while fourth one is having space for new transaction so transaction filling start from fourth VLF (FSeqNo 36) when fourth VLF (FSeqNo 36) filled completely then SQL  check that is there sufficient VLFs are free for the transaction and ready to used? SQL checks these VLF in circular fashion. If VLFs are free then it start using them. If VLFs are not free then it will grow transaction log as per the autogwoth configuration and this will create new VLFs for further use.

Here after filling fourth VLF(FSeqNo 36) it start using First VLF and change FSeqNo 33 to 37, status 0 to 2 i.e. inactive to active, parity 64 to 128. VLF FSeqNo change from 33 to 37 which represents the circular behaviour of VLF Files.

Here parity values also chage from 64 to 128. Actually these value will fluctuate as SQL Server uses the VLF if any VLF is having parity value 0 then it will change to 64, if VLF having parity value 64 then it will change to 128, again if VLF is having parity value 128 then it will change to 64.

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook