Search code examples
sql-serversql-server-2005sql-server-2008transaction-log

SQL Server: what is the virtual log file?

What is the Virtual Transaction Log file in SQL Server?

Solution

  • From MSDN:

    The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.

    The transaction log is a wrap-around file. For example, consider a database with one physical log file 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).

    alt text

    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.

    alt text