SQL Server Power Search - Get More Relevant Results

Virtual Log File

by Rahul M.
(PA)

What is a virtual log file? We have just been told by a consultant that we have too many for one of our databases and we need to reduce them to get better performance.

What does this mean, and what steps should I take to do this.

ANSWER:
Internally a transaction log consists of multiple blocks, called virtual log files. Every time a transaction log grows, it is done by adding additional virtual log files. The number added varies from 4 to 16, depending upon the size the log is grown by.

The problem is that a transaction log created with the default size and growth may grow repeatedly, resulting in hundreds of virtual log files. You can see how many you have with the DBCC LOGINFO command.

The reason this is bad for performance is due to the nature of log writes. The transaction log is written sequentially, and if it is on a dedicated disk the disk head remains static between writes (no seek time). Multiple virtual log files may be scattered over the disk, resulting in a seek every time a write crosses from one file to the next. This increases write latency.

The solution is to backup the transaction log, (or perform a checkpoint if the recovery model is Simple), shrink the log file to a minimum size, and then immediately grow it to the biggest size you think you need. This should result in, at most, 20 or 30 VLFs. If you have the opportunity to defragment the disk before doing this, even better.

The commands are:

BACKUP LOG dbname TO DISK='backup_path'

(or CHECKPOINT if recovery model is Simple)

DBCC SHRINKFILE (log_logical_file, 1)

ALTER DATABASE dbname
MODIFY FILE
(NAME=log_logical_file, SIZE=new_size)

Comments for Virtual Log File

Click here to add your own comments

Jan 08, 2010
Eliminating Virtual Files? Not so much
by: Anonymous

I experimented with the procedure suggested for eliminating Virtual Log Files.

I took a database in SIMPLE Recovery mode, with 93% free space in the DAT file.

I Used DBCC LOGINFO to find the current number of files:

79 virtual files

I successfully Checkpointed and shrank the DAT file, then reset the size to 10x the shrunk value.

Ran DBCC LOGINFO again:

87 virtual files

Dec 05, 2010
LOG FILES, NOT DATA FILES
by: Randy

This is about manipulating the log file, typically has an LDF file extension. It's not about shrinking or changing the sql data files -- usually an MDF or NDF file extension.

Feb 14, 2011
About VLF's
by: Uday

Too many VLF's can definitely degrade the performance , make sure about
log file auto growth i,e if you set low value that may create too many VLF's that may cause the performance..
addition to above -database recovery taking far longer than expected when a log file contains too many VLFs.

Best Practise
------------
**set high value (atleast ~1gb is good for OLTP) of auto growth.

**schedule every 15 minutes or frequent log backup...

How to reduce too many VLF's
=======================
you can go for shrinking during non-peak hours.....


Note ->Before making setting make sure you have an good plan in your Hand..
Best Regards,
Uday
========================

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to SQL FAQ.