Ashish MehtaEvery transaction log file is logically divided into smaller segments and these segments are called Virtual Log Files or VLFs.
VLF stands for Virtual Log File in SQL Server Transaction Log File
How Virtual Log Files Work Internally within Transaction Log File in SQL Server?
SQL Server Database Engine defines the size of virtual log files dynamically while creating or extending transaction log files in SQL Server. Virtual log files will not have a fixed size and one cannot predict the number of VLFs within the transaction 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.
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 transaction 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
Trending Transaction Log Articles and Tips
• 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 Transaction Log Backup in SQL Server
• Monitoring Transaction Log File Space Utilization in SQL Server
• Tail Log Backup of Transaction Log in SQL Server
How does SQL Server Transaction 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.“
Reference
Trending SQL Server Disaster Recovery Articles and Tips
- How to Repair Suspect Database in SQL Server
- SQL Server: How to Start SQL Server with Minimal Configuration
- How to Start SQL Server in Single User Mode?
- How to Identify the Location of Resource Database in SQL Server
- What are Virtual Log Files in SQL Server Transaction Log File?
- How to Backup and Restore Resource Database in SQL Server
- Steps to Connect to SQL Server When all System Administrators are Locked Out
- How to Use Dedicated Administrator Connection in SQL Server
- Different Ways to Enable Dedicated Administrator Connection in SQL Server
- Why to Configure Database Instant File Initialization in SQL Server? Advantage
Chetna Bhalla