SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

What are Virtual Log Files in SQL Server Transaction Log File?

Every 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.

Virtual Log File in SQL Server Transaction Log

(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.

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

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement