MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































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

Read Comments   |   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

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

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

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

Reference:

More... Disaster Recovery Tips for DBAs and Developers







Last Updated On: May 01, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: