Understanding SQL Server Transaction Log File Architecture

Today, i will talk about SQL Server Transaction Log File Architecture. As we know, every database must have at least one transaction log file (Tlog file) that captures everything happens on database to maintain data integrity of the database. Logically, SQL Server transaction log file is a series of log records whereas physically these log records stored in this transaction log file that are placed on OS drive.  I will discuss about the SQL Server transaction log file architecture in this article. Let us start with logical architecture of Tlog file followed by the physical architecture.

Related Articles:

Logical Architecture

To understand SQL Server Transaction Log File Architecture, we need to start with basics. As we know transaction log file is mandatory database file for a database. Every database must have at least one transaction log file. SQL Server uses transaction log file to capture log records that guarantee the data integrity of the database and for data recovery.

Logically, SQL Server transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written with the incremental LSN that means a new log record will be assigned a LSN that is higher than the LSN of the record before it. Log records are stored in a serial sequence as they are created.

Operations that recorded in the Transaction Log File

There are many types of operations recorded in Tlog files. These operations include:

  1. The start and end of each transaction.
  2. Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
  3. Every extent and page allocation or deallocation.
  4. Creating or dropping a table or index.
  5. Rollback operations. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. This reserved space is freed when the transaction is completed.

The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to do a full recovery of the database. You can not truncate any part of active log. 

Physical Architecture

As we know, a series of log records stores in transaction log file. These log files store physically on OS drive/disk along with SQL Server data files. Each transaction log file internally divided 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 size or number of virtual log files cannot be configured by administrators. You can get total count of virtual log files or vlfs in a SQL Server database log file/s in attached link. Here, i have explained in detail about how to track virtual log files.

The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log 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.

If we define small Tlog file size along with small growth increment then it will cause performance issue to your system because whenever log file needs more room, it will increase log file size frequently due to small size of growth increment so it will create lot of virtual log files as well. This will affect performance of backup and restore operation along with database startup performance.

Microsoft recommends that we should assign log files a size value close to the final size required, and also have a relatively large growth increment value.

How Transactions Log File works?

The transaction log is a wrap-around file. Let us demonstrate it using a scenario given in Microsoft Book Online.

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). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback.

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 frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

  1. If the FILEGROWTH setting 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.
  2. If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, a 9002 error is generated.

If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical tlog file.

You can follow attached link to understand SQL Server data files, Pages & Extents.  I hope you like this article. Here i have described SQL Server Transaction Log File Architecture that will help you to understand SQL Server database system. Please follow us on our facebook page and on Twitter handle to get latest updates.

Read More:

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *