Transaction Log

The transaction log is a crucial part of each database that records all transactions and database changes made by each transaction. The purpose of the transaction log is to keep record of all changes that were made in the database. Let’s consider the example from the picture below:

1. The user makes some changes in the database.
2. This query is executed and this operation is immediately recorded in the transaction log.
3. When the record is saved, the query returns to the user. But what about the record in the data file?
4. Data file is later updated during a checkpoint. It works this way because the data file may need to expand to accommodate the changes.

One more thing that has to be mentioned is that a database can have one or more transaction log files. Using two or more transaction logs will not make your database work faster, because writing can occur only into one file at a time, meaning that parallel I/O operations are not possible. The scenario under which multiple transaction log files are recommended is when the first one can not grow anymore.

Virtual Log Files

The transaction log stores every transaction made in a database, except those that are minimally logged (such as SELECT INTO or BULK IMPORT). Each transaction log record has its own unique number, called the Log Sequence Number (LSN), and is stored in the transaction log’s Virtual Log File (VLF), whose size is not fixed. A transaction log can have any number of virtual log files:

Transaction Log Truncation

The log file will grow until it fills all free space on the disk where it is located unless log records are removed. As time passes, old log records become unnecessary for the recovery process, so they must be deleted to free space for new log records. Such a process in SQL Server where all unnecessary records are removed to reduce the size of the log file is called transaction log truncation.

The way this truncation happens is dependent on the recovery model that was selected for your database.

Simple Recovery Model

The transaction log is truncated when checkpoint occurs. At that time, all committed transactions are written to the data file and virtual log files can be reused. Look at the picture below: 

After the checkpoint occurs, virtual log files 1 and 2 are no longer in use because transactions 11 and 12 have been committed. SQL Server marks virtual log files 1 and 2 as reusable. Such a process is known as truncation of the transaction log. All committed transactions have been truncated, but the physical size of the transaction log remains the same. If there is a need to make the transaction log file smaller, turn AUTO_SHRINK option on and it will physically shrink the log file (where possible) at periodic intervals:

ALTER DATABASE your_database SET AUTO_SHRINK ON
ALTER DATABASE your_database SET AUTO_SHRINK OFF

To set up auto shrink using SSMS, right-click on the database for which you want to set up transaction log auto shrink, select “Properties”, then “Options”. Choose “Auto Shrink” and switch from “False” to “True”, and then click “OK”.

So after the transaction has been committed, SQL Server can reuse the space. But be careful with this option as there is no way to control when auto shrink starts. Also, such operations where the file grows then shrinks, then again grows, and again shrinks, take more resources.

Full or Bulk-Logged Recovery Models

In this case, the transaction log is truncated only during the transaction log backup. In the full or bulk-logged recovery model when the checkpoint occurs, all dirty pages are written to the disk, but SQL Server will not mark any of the virtual log files as reusable. The transaction log will be truncated only after the transaction log backup is completed:

BACKUP LOG your_database TO DISK = 'log.bak'

Remember, if the database is running under the full or bulk-logged recovery model and the full backup has been never made, the transaction log will be truncated at each checkpoint.

Maintenance of the Transaction Log

As mentioned above, the transaction log is automatically truncated under the simple recovery model, but how can you manage the transaction log if the database uses a full or bulk-logged recovery model? This is achieved through regular transaction log backups.

Moreover, it is recommended to monitor the transaction log space. Use the following command:

DBCC SQLPERF(LOGSPACE)

That returns the following table:

• Database Name – name of the database
• Log Size (MB) – current size of transaction log
• Log Space Used (%) – shows how the percentage occupied with transaction log information in the log file
• Status – log file status (always ‘0’)

Another useful command that can help to find out how many virtual logs are in the transaction log file that is being currently used:

DBCC LOGINFO

• FileId – shows in which physical file the VLF is stored.
• FileSize – size of the transaction log file (in bytes).
• StartOffset –  used as the sort column for the output. Note that the first VLF is always 8,192 bytes.
• FSeqNo (File Sequence Number) – indicates the order of usage of the VLF. The VLF with the highest FSeqNo number is the VLF where current log records are being written.
• Status – there are two possible values: 0 and 2. VLF with 0 indicates that it can be reused, the VLF with value 2 indicates that it is not reusable.
• Parity – has two values: 64 and 128. It switches every time when a VLF is reused.
• CreateLSN – indicates when the VLF was created. If the value is 0 it means that VLF was created when the database was created. If the VLFs have the same value it means that they were created at the same time.