A SQL Server transaction log file is the 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.
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.
SQL Server continues to write the new transactions from the beginning of the transaction. If the the transaction log file is full it 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 or autogrowth property is not enabled for log file SQL Server will report the following error: Error: The log file for database ‘%.*ls’ is full.
The size of log file is given at the time of database creation in SQL Server. Internally, SQL Server divides the space in a transaction log file into a number of ‘chunks’ called virtual log files.
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. 50 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 the user.
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.
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.
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.
Log truncation process automatically frees space in the logical log(virtual log files) for reuse by the transaction log. So the physical size of the log file will not change until log growth is happens.
During log truncation process ( Truncation process usually occurs during checkpoint or it may also occur during log backup etc.) the dirty pages in the memory and log records in the log file are updated to the disk (original pages ) of data file and once after the data file updation, virtual log files will be cleared and be available for future transaction .
Got questions? Email us at: vembu-support@vembu.com for answers.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
