SQL Server: What are Virtual Log Files in SQL Server Transaction Log File?
Related Topics: SQL Server Disaster Recovery Tips>
Next Topic: SQL Server Indexing Articles & Tips
What is Virtual Log File (VLF) in Transaction Log File?
Every transactional log file is logically divided into smaller segments and these segments are called Virtual Log Files or VLFs.
How Virtual Log Files Work Internally within Transactional Log File in SQL Server?
SQL Server Database Engine defines the size of virtual log files dynamically while creating or extending transactional log files in SQL Server. Virtual log files will not have a fixed size and one cannot predict the number of VLFs within the transactional log file. Internally, SQL Server will always try to maintain a smaller number of VLFs. However, there is no way for a database administrator to configure or set a fixed size for VLFs in SQL Server.
Virtual log files are truncated only when there no records of an active transactions. The space released after the truncation of VLFs will be made available for new transactions. If the transaction log file increments are very small then this will result in smaller and large number of VLFs within the SQL Server transactional log file thereby resulting in Performance issue.
Impact of Large number of Virtual Log Files in SQL Server Transaction Log File
- Inserts, Updates and Delete operations can take long time to complete
- Transaction Log backups will take longer time to complete
- Restoring database may take longer time
- Starting up an SQL Server Database may take longer time
• How to Identify the Number of Virtual Log Files in SQL Server Transaction Log File
• How to Reduce the Number of Virtual Log Files in SQL Server Transaction Log File
• How to Change SQL Server Database Auto Growth Settings to Reduce Virtual Log Files
• How to Configuring Database Instant File Initialization Feature of SQL Server
• How to Take Transactional Log Backup in SQL Server
• Monitoring Transactional Log File Space Utilization in SQL Server
• Tail Log Backup of Transaction Log in SQL Server
How does SQL Server Transactional Log File Work in SQL Server?
TechNet Describes SQL Server Transaction Log File Architecture as below:
"Consider a database with one physical log file which is divided into four virtual log files.
- When the database is created, the logical log file begins at the start of the physical log file.
- New log records are added at the end of the logical log and expand toward the end of the physical log.
- Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN).
- The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback.
The transaction log in the example database would look similar to the one in the following illustration.
(Image Credits: TechNet)
When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.
(Image Credits: TechNet)
This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills."
Thank you for taking your time to read
this article. Let's be Connected....
More... Disaster Recovery Tips for DBAs and Developers
- Different Ways to Retrieve SQL Server Configuration Details
- How to Move TempDB Database Files to a New Drive in SQL Server
- Improved Startup Parameters in SQL Server 2012
- Steps to Connect to SQL Server When all System Administrators are Locked Out
- How to Repair Suspect Database in SQL Server
- How to Connect to a Named Instance of SQL Server
- Introduction to SQL Server Configuration Manager
- SQL Server Disaster Recovery Tips for DBAs and Developers
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
Last Updated On: May 01, 2016
Please leave your Valuable Comment or Let us know how this article helped you: