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)