What are virtual log files in a SQL Server transaction log?
May 16, 2014 byIvan Stankovic
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.
TheFullrecovery 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.
TheBulk loggedrecovery 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 doesnt break thetransaction
log backup chain.
TheSimplerecovery 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 readonly 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 hasnt 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 cant 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 cant
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 autogrowth 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 autogrowth settings are not properly managed, a SQL Server database can be forced to autogrow,
which can cause serious performance issues. SQL Server will stop all processing until the autogrow event
is finished. Due to physical organization of the hard drive, the autogrowth 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 autogrowth option, as these vary from
case to case. Having too many or too little virtual log files causes bad performance.
There isnt an option in SQL Server Management Studio which can provide the number of virtual log files.
The virtual log files can be shown via TSQL 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 autogrow 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 hasnt been backed up yet.
