The log file is one of those parts of SQL Server I find fascinating. For instance it is composed of VLFs (virtual log files). These VLFs are a logical way for SQL to break the physical file down into smaller pieces. These pieces are then marked as “in use” or “available”. As the each piece (VLF) is used SQL begins writing to the next.

They are used in order, but when the last one has been used then SQL moves back to the first one again. Because of this the transaction log is considered circular.

If it gets to the last VLF, tries to use the first VLF and it’s in use, the LOG grows and new VLFs are created.

So what happens if the last VLF is in use and the first few are cleared. We then use up all of the log space (VLFs 1-3 in my image).

In theory we would now grow and the current order of the data in the VLFs will be 4,1,2,3,5 etc. This isn’t quite in order and looks a little funny so let’s try a test.

-- Setup code
USE master
GO
CREATE DATABASE [LogTests]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LogTests', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\LogTests.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'LogTests_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\LogTests_log.ldf' , SIZE = 8MB , MAXSIZE = 81920KB , FILEGROWTH = 8MB );
GO

Once we have created the database we need to take an initial backup to keep it out of pseduo simple mode.

BACKUP DATABASE LogTests TO DISK = 'c:\temp\Logtests.bak';
GO

Next we can fill some of the VLFs by creating a table and running some transactions by inserting data into it.

USE LogTests
GO
CREATE TABLE LogLoadTest (VarCol char(1000));
GO
INSERT INTO LogLoadTest VALUES (REPLICATE('A',1000));
GO 4000
DBCC loginfo;
GO

Note that the status of all the VLFs is 2. This means that they are all “in use”. (“Available” VLFs show a status of 0.) Next we take a log backup in order to clear the first three VLFs.

BACKUP LOG LogTests TO DISK = 'c:\temp\Logtests.log';
GO
DBCC loginfo;
GO

Now the only VLF that is “in use” is the fourth one and the rest are “available”. Let’s add a few more transactions to fill the first couple of VLFs back up again.

INSERT INTO LogLoadTest VALUES (REPLICATE('A',1000));
GO 3000
DBCC loginfo
GO

Now the first, second and fourth VLFs are in use and the third is the only one available. Let’s add a few more transactions to fill up the third VLF.

INSERT INTO LogLoadTest VALUES (REPLICATE('A',1000));
GO 1000
DBCC loginfo
GO

Ok, so all of the VLFs are in use now. At this point when we add some more transactions the log is going to have to grow and it’s going to have to grow at the end.

INSERT INTO LogLoadTest VALUES (REPLICATE('A',1000));
GO 1000
DBCC loginfo
GO

As we can see the log file grew and added an additional four VLFs. We can tell the order of the VLFs by looking at the FSeqNo column. 38,39,40,37,41. So yes, because of the way the log grew the VLFs are temporarily out of order. I say temporarily because if we were to take a log backup and begin running transactions the jump would clear out and the VLFs would again be used in order.