Virtual Log Files

Each transaction log file is divided logically into smaller segments called virtual log files (VLFs). 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. VLF can be in 4 states

1.Active

2.Inactive and not backed up (or truncated) - recoverable

3.Inactive and backed up or truncated - recycable

4.Unused

DBCC LOGINFO shows a list of virtual log files that are active logs when their status flag is 2. The sample sql is

DBCC LOGINFO('Database_name')

This returns one row per VLF.

Number of rows = Number of VLFs

Sample output from this command.

FileId,FileSize,StartOffset,FSeqNo,Status,Parity,CreateLSN
2,253952,8192,21,0,128,0
2,253952,262144,22,0,128,0
2,253952,516096,20,0,64,0
2,278528,770048,23,2,64,0

The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files.

It is good to Minimize VLF’s. Excessive autogrowth of the transaction log causes:

* Windows call to extend a file (may cause file fragmentation)
* Adds “VLFs” to the file on each autogrowth

If there are excessive VLFs (> 50) then the first cause of action should be to free up log space by first clearing space from the transaction log (using BACKUP LOG)

Another useful command is DBCC SQLPERF (logspace) which shows the transaction log size and log space used percentage.