Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Network Performance Monitor (NPM) > About SQL Server transactional and virtual log files

About SQL Server transactional and virtual log files

Updated August 1st, 2016

Overview

This article provides information about virtual log files in a Microsoft SQL Server transaction log. This article also addresses an issue where several virtual log files display within the Orion database.

Environment

  • All Core products

Detail

The number of virtual log files is not limited or fixed per transaction log file. Also, virtual logs do not have a fixed size. If one file is 512KB, all remaining files will be the same size. 

 

SQL Server determines the size of a virtual log file when the transaction log file is created or extended. The goal is to maintain the small number of virtual log files in the transaction log file to streamline SQL Server file handling. The size or number of virtual log files cannot be configured or set by a database administrator. 

SQL Server transaction log files 

An SQL Server transaction log records all transactions that occur in an SQL Server database. Each database transaction is written in sequential order into the transaction log. The SQL transaction log maintains a backup copy of previous values, which can be useful for disaster recovery. Log file maintenance varies, depending on your recovery model. 

 

An SQL Server transaction log file is an integral part of every SQL Server database. The database includes .MDF data files and .LDF transaction log files. 

An SQL Server database can have one active .LDF file with additional.NDF database files. You can have additional .LDF files, but this configuration is only recommended in specific situations. In either case, SQL Server can only have one active .LDF file at a time. 

Available recovery models in SQL Server

SQL Server includes three recovery models: Simple, Full, and Bulk logged.

The Full recovery model is best suited 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 (for example, backed up regularly). 

 

The Bulk logged recovery model is used where large bulk operations are required, such as bulk import or index recreation. It is designed to log most bulk operations and save the amount of space needed for the SQL transaction log file. You can use this recovery model with the Full model when planning bulk operations. Switching between these two recovery models does not break the transaction log backup chain.

 

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

Diagram showing that the SQL Server transaction log is a wrap-around file

Writing an SQL Server transaction log

If the transactions in a Full recovery model fill up the transactions log file, SQL Server will continue to write the new transactions from the beginning of the transaction log file only if the the transaction log backup is taken and the old transactions are marked to be deleted. If the transaction log backup is not 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 cannot grow due to limited hard drive space, SQL Server report the following error:

Error: 9002, Severity: 17, State: 2

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

Virtual log files (VLFs)

Each SQL Server transaction log file consists of smaller files called virtual log files. The number of virtual log files is not limited or fixed per transaction log file. These files do not have a fixed size. If one file is 512KB, all other files will be the same size. 

File size

SQL Server determinates the virtual log file size 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 can process smaller quantities of files more efficiently.  The size or number of virtual log files cannot be configured or set by a database administrator.

 

By default, the SQL Server transaction log file is set at an initial size of 2MB with a 10% default growth value of the current size. When you create an SQL Server database, you can modify these options to accommodate your database use. The auto-growth option is optional and turned on by default. You can select the file growth in megabytes or percent and limit the maximum file size. By default, SQL Server creates a database with unrestricted file growth.

Auto growth

If the auto-growth settings are not managed properly, an SQL Server database can auto-grow exponentially, causing serious performance issues. SQL Server will stop all processing until the auto-grow event is finished. Due to the physical organization of the hard drive, the auto-growth event will take up additional space that is larger than the previous transaction log file. This process leads to file fragmentation and slower slower response.

 

There is no general rule 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 will degrade your performance.

 

SQL Server Management Studio does not include an option to determine the number of virtual log files. You can view virtual log files using T-SQL script for each SQL Server database. An additional article will describe this topic in detail.

 

You can increase the number of virtual log files using an auto-grow event. This is a common process, but requires strict rules to avoid unplanned problems with space or unresponsiveness during peak hours. You can decrease the number of virtual log files by shrinking the SQL Server transaction log file, which also requires strict rules to avoid deleting the data waiting to be backed up. .

 

 

Last modified
16:44, 6 Mar 2017

Tags

Classifications

Public