448 Virtual Log Files (VLFs) on a 11gb file – Hmmmmmm

What is a VLF?

Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions

Why did this happen?

It seems as though whoever created the database, didn’t look into capacity planning and size the database accordingly. this has resulting in a lot of 10% autogrows, which in turn has resulted in some very small VLF’s and then some very big VLFs.

Performance Impact?

From Books Online (http://msdn.microsoft.com/en-us/library/ms179355.aspx):

The only time virtual log files affect system performance is if the log files are defined by small size and growth_increment values. If these log files grow to a large size because of many small increments, they will have lots of virtual log files. This can slow down database startup and also log backup and restore operations. We recommend that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value

Read here for Test Case – Performance Impact: a large number of virtual Log files

and also here – Performance Impact: a large number of virtual Log files II

and also here – How a log file structure can affect database recovery time

How are VLF sizes decided?

It all depends on the amount of space that is added not the total size of the log. For example, log growth of 1mb (whether manual or autogrow) will ask for 4 VLFs each 256kb, but an autogrow of 1GB will request 8 chunks each 128mb.

chunks less than 64MB = 4 VLFs

chunks of 64MB and less than 1GB = 8 VLFs

chunks of 1GB and larger = 16 VLFs

How to resolve?

After reading Kimberly Tripps Guide to managing Transaction Logs – ‘8 Steps to better Transaction Log throughput’, i started looking through some of my more heavily used databases and found that one of the main databases had a log file of 11gb split into 448 VLF’s! If you put this in the context of what Kimberly suggests:

In general, most transaction logs will only have 20 or 30 VLFs – even 50 could be reasonable depending on the total size of the transaction log.

Then 448 is a large number for a fairly small transaction log (around 10% of the size of the database).

DBCC LOGINFO

Running the above command tells you how many VLF’s are in your transaction log for a particular database.

Following the advice given in the article, I truncated the log to 1mb:

USE [mydatabase]
GO
DBCC SHRINKFILE (N'mydatabase_log' , 0, TRUNCATEONLY)
GO

Then resized so that

the first 1gb had a VLF size of 128mb, and the rest had chunk sizes of 448mb up to 8GB.

So, to get the initial 8 chunks of 128mb, i ran:

ALTER DATABASE MyDatabase
MODIFY FILE 
( 
      NAME = 'MyDatabase_log'
    , SIZE = 1024MB
)

Then extend again up to 8gb to create the extra 16 partitions for the 7gb of addtional space:

ALTER DATABASE MyDatabase
MODIFY FILE 
( 
      NAME = 'MyDatabase_log'
    , SIZE = 8192MB
)

The end result is 24 more manageable chunks instead of 448!

Advertisements

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

%d bloggers like this: