What is a SQL Server transaction log file?

A SQL Server transaction log file is an integral part of every SQL Server database. Along with the data file, which has an MDF extension, the transaction log file with a LDF extension, makes a SQL Server database.

A SQL Server database can have more than one data file. These additional data files have an NDF extension. But there can be only one active LDF. Physically, there can be additional LDF files but this is only recommended in specific situations. In either case, SQL Server can only use one LDF file at the time.

What does a SQL Server transaction log do?

A SQL Server transaction log keeps all transactions that occur in a SQL Server database. With every database transaction, a log record is written into the transaction log sequentially. The purpose of the SQL transaction log is to bring back old values if ever needed, which can be useful in disaster recovery situations. Depending on the recovery model, the transaction log file is maintained differently.

What are the available recovery models in SQL Server?

There are three recovery models available in SQL Server: Simple, Full, and Bulk logged.

The Full recovery model is the most suitable for production systems where data loss is unacceptable. The old transactions are marked for reuse after a transaction log backup. The SQL transaction log can grow unless it is maintained properly, i.e. backed up regularly.

The Bulk logged recovery model is used when there is a need for large bulk operations such as bulk import or index recreation. It is designed to minimally log most bulk operations and save the amount of space needed for the SQL transaction log file. This recovery model can be used with the Full model when bulk operations are planned. Switching between these two recovery models doesn’t break the transaction log backup chain.

The Simple recovery model if often used in testing and development environments, where data loss is not an issue. The older transactions are marked for reuse and will be overwritten with new ones automatically. In this recovery model, there is a little chance for transaction log file growth because the file is self-maintained. If a disaster occurs, there is a chance of data loss. Also, this recovery model is suitable for data warehouses with read-only data.

How is a SQL Server transaction log written?

When using the Full recovery model, if the transactions fill up the transaction log file, SQL Server continues to write the new transactions from the beginning of the transaction log file, but only if the transaction log backup is taken and the old transactions are marked for deletion. If the transaction log backup hasn’t been taken, the transaction log file is forced to expand and grow enough to accommodate newly executed transactions. This operation is performance intensive.

If the SQL Server transaction log file can’t grow due to a limited free space on the hard drive, SQL Server will report the following error: Error: 9002, Severity: 17, State: 2.

If there is more than one transaction log file, SQL Server will move through all the files first, before it starts to write new transactions to the first file. Maximum size for a transaction log file is 2 terabytes.

What are virtual log files – VLF?

Each SQL Server transaction log file is made of smaller parts called virtual log files. The number of virtual log files is not limited or fixed per transaction log file. Also, there is no fixed size of virtual log file, if one is e.g. 512 kilobytes, all other will be of the same size.

SQL Server determinates the size of a virtual log file dynamically when the transaction log file is created or extended. The goal is to maintain the small number of the virtual log files in the transaction log file, because SQL Server handles the smaller number of files easier. The size or number of virtual log files can’t be configured or set by a database administrator.

By default, the SQL Server transaction log file is set at an initial size of 2MB. Also, the default growth value is 10% of the current size. While creating a SQL Server database, these options can be modified to accommodate planned needs for the database. The auto-growth option is optional and turned on by default. File growth can be specified in megabytes or percent. There is also the clause to limit the maximum file size. By default, SQL Server creates a database with unrestricted file growth.

If the auto-growth settings are not properly managed, a SQL Server database can be forced to auto-grow, which can cause serious performance issues. SQL Server will stop all processing until the auto-grow event is finished. Due to physical organization of the hard drive, the auto-growth event will take up the space which is not close physically to the previous one occupied by the transaction log file. This leads to the physical fragmentation of the files which also causes slower response.

There is no general rule how to determine the best values for the auto-growth option, as these vary from case to case. Having too many or too little virtual log files causes bad performance.

There isn’t an option in SQL Server Management Studio which can provide the number of virtual log files. The virtual log files can be shown via T-SQL script for each SQL Server database. There will be other blog post which describes this topic in detail.

The number of virtual log files can be increased by an auto-grow event, this process is common, but it needs strict rules to avoid unplanned problems with space or unresponsiveness in peak hours. The number of virtual log files can be decreased by shrinking the SQL Server transaction log file, which also requires strict rules to avoid deleting the data which hasn’t been backed up yet.