Virtual Log Files

What are Virtual Log Files (vlf’s)

The transaction log within in sql database is considered a ‘wrap around’ file. Data is written within the log file, cleared down, then the space is reused. The image below represents a transaction log file, with the virtual log files within it.

Think of virtual log files as ‘mini’ files within the transaction log that sql server writes to when a transaction takes place. Once that ‘mini’ file is full, sql server will use the next vlf to store the next bit of data. The only way that vlf can become ‘usable’ again is when a checkpoint occurs. This checkpoint ‘truncates’ the vlf (it does not delete the information stored), making it ready to store more data. If the checkpoint never occurs, then when the end of the logical log ‘catches up’ with the start of the logical log  as data is written, then the transaction log becomes full and one of two things can then happen.

• if there is physical space on the disc where the transaction log is located, it will auto grow according to the settings you have configured for the log (or use the default settings if you have not changed them). This will cause more vlf’s to be injected into the transaction log file (we look at this in a moment)
• if the log file cannot auto grow, either because there is no disc space or you do not have auto grow enabled, then sql server will throw error 9002. In this scenario the database becomes read only as no more data can be written to the log file.

We will look at how to fix a full transaction log in another article but for now let us concentrate on the subject of virtual log files.

When Does a Checkpoint Occur

So, we can see the importance of Checkpoint process. This will clear down the virtual log files and enable sql server to keep using the transaction log to store transactions without ever having to grow it. When a checkpoint is issued, the inactive portion of the log is marked as reusable and can be used to write to. Once it is being written to is is said to be active, until the next checkpoint occurs. It is this active portion of the log that sql server users to recover the database.

The checkpoint frequency is dependant on the ‘Recovery model’ of your database. If the database is in ‘Full’ or ‘Bulk logged’ recover mode, then the checkpoint occurs when your scheduled log backup job occurs. You don’t have a log backup job running ? Then, your log file will never clear down the virtual log files, in other words they will remain ‘Active’ and the log file will either grow or become full as described in bullet points above. This is why it is so important to issue regular log backups if your database is using the ‘Full’ or ‘Bulk logged’ recovery model.

If your database is using the ‘Simple’ recovery model then SQL server automatically generates the checkpoint. You can read up on the subject at

 

https://technet.microsoft.com/en-us/library/ms189573(v=sql.120).aspx

 

Virtual Log file Growth And The Impacts on Your Database

So, as we have discussed, if the log file needs to grow, then it also adds more Virtual lOg files to your transaction log. So what effect can this have on your database performance ?

Firstly it should be noted there is no magic number for the amount of vlf’s that a lot file should contain.

Too many vlf’s can cause transaction log backups to slow down (you may see an increase in your WRITE LOG wait type in this case), slow down your database recovery times, and in some cases cause your Inserts, Updates and Deletes to slow down.

You can see how many vlf’s are held within your transaction log by running:

DBCC loginfo 

within the context of your database.

When you first create your transaction log the VLF’s created are based on an algorithm as described below: (Taken from Paul Randals blog post which has more information)

Versions Upto SQL2014

• Up to 64Mb – 4 new vlf’s, each roughly 1/4 the size of the growth
• 64Mb to 1Gb – 8 new vlf’s, each roughly 1/8 the size of the growth
• Over 1Gb – 16 new vlf’s, each roughly 1/16 the size of the growth

For SQL2014

• Is the growth size less than 1/8 the size of the current log size ?
• Yes – create 1 new vlf equal to the growth size
• No – use the formula above.

The amount of vlf’s that is considered to many really depends on the size of your log file.

See this article by Kim Tripp entitled ‘Transaction Log VLF’s – too many or too few?’

For example a 5Gb log file with over 200 vlf’s is probably too many.

A database with a 30Gb log file with thousands of vlf’s is probably too many.

You can also have too few vlf’s. If a database has 50Gb log file and only 10 vlf’s then each vlf would be 10Gb in size meaning that the log file could only clear at 10Gb of log info and when it was completely inactive.