Transaction Log Physical Architecture
The transaction log in a database maps over one or more physical files. Conceptually, the log file is a serial string of log records. Physically, the sequence of log records must be stored efficiently in the set of physical files that implement the transaction log.
Microsoft® SQL Server™ 2000 segments 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. SQL Server chooses the size of the virtual log files dynamically while creating or extending log files. SQL Server tries to maintain a small number of virtual files. The size of the virtual files after a log file name extension is based on 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; it is determined dynamically by the SQL Server code.
The only time virtual log files affect system performance is if the log files are defined with small size and growth_increment values. If these log files grow to a large size through many small increments, they will have a lot of virtual log files, which can slow down recovery. It is recommended that log files be defined with a size value close to the final size needed, and also have a relatively large growth_increment value.
The transaction log is a wrap-around log 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, which grows toward the end of the physical log. As truncation operations occur, the records in the virtual logs before the minimum recovery log sequence number (MinLSN) are deleted. The log in the example database would look like the one in the illustration.
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.
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 often enough to always leave enough room for all the new log records created through the next checkpoint, the log never fills. If the end of the logical log does reach the start of the logical log, however, one of two things happens:
- If autogrow is enabled for the log and space is available on the disk, the file is extended by the amount specified in growth_increment and the new log records are added to the extension.
- If autogrow is not enabled, or the disk holding the log file has less free space than the amount specified in growth_increment, an 1105 error is generated.
If the log contains multiple physical log files, then the logical log will move through all of the physical log files before it wraps back to the start of the first physical log file.