The Database Transaction Log - Part 2: How the Transaction Log Works

The transaction log files are different from the database data files in the way that they are used, as well as how the space is allocated inside of them.  The Log files, unlike standard data files are written to sequentially.  For this reason, it is often best for the logs to be on dedicated disks for log use only.  Since the IO is all sequential, random I/O from the data files could impact performance of logging.  The log files are subdivided internally into Virtual Log Files as shown in the below picture from the Books Online:

The number of VLF's in the transaction log can be found using DBCC LOGINFO.  This is an undocumented command, but it provides some very useful information regarding the transaction log for a specific database.  The following image shows the output of DBCC LOGINFO for AdventureWorks on my laptop:

As can be seen in the image, there are 4 VLF's in the transaction log.  The FSeqNo provides the logical order of the VLF's inside of the physical log files.  The Status column provides information as to whether a specific VLF is currently being used (2) or if it is available for use (0).  FileSize and offset provide information about the actual location of the VLF inside the physical file structure, and how large the VLF actually is. The actual sizing of the VLF is important to pay attention to.  If your VLF's are to small, then you will have excessive numbers of VLF's in your log files.  If they are to large, then they won't truncate free space effectively or efficiently. 

As mentioned previously, the transaction log is a sequentially written file that is used in a round robin fashion.  The start of the log file may not be the current start of the logical log.  The following image from the Books Online shows how a single log file with four virtual log files is used by the database server.

The start of the logical log in this case is at the beginning of the third VLF.  Since the log is written sequentially, the log moves from the start of the third VLF through the fourth VLF and when it reaches the end of the file, starts back over at the beginning of the log file if there is free space.  So long as the end of the logical log never gets back to the start of the logical log, the transaction log will stay the same size.

In a two file system, the files are used sequentially, and unlike the database data files which are striped.  With two transaction log files, the first file is used, and then the second file is written to following the below picture:

As long as the log space is truncated, the logs can continue to be used in a round robin fashion that prevents growth from being required.  The mechanism for truncation differs depending on the recovery model selected for the database in question.  If the database is in FULL recovery,  then the log is truncated when it is backed up using the BACKUP LOG command.  In SIMPLE recovery, the log truncation occurs on checkpoint for all complete transactions.  The active portion of the transaction log should remain fairly small for SIMPLE Recovery.  Exceptions to this would be large long running transactions as covered in my previous posting.