Playing with the log – VLF order while growing the log

1

December 17, 2014 by Kenneth Fisher

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.

VLFOrder1

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.

VLFOrder

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.

VLFOrder2

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

VLFOrder3

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

VLFOrder4

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

VLFOrder5

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

VLFOrder6

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

VLFOrder7

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

VLFOrder8

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.

One thought on “Playing with the log – VLF order while growing the log

  1. […] to use Powershell Workflow instead of SSIS Thoughts in vNext deployment in Release Management Playing with the log – VLF order while growing the log What, When and who? Auditing 101 Machine Learning Oliver Script: A Holiday Tale–Part 1-7 Querying […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,653 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: