SQL Server Transaction Log Architecture

Every SQL database has a transaction log that records all transaction.The trasaction log is the critical component of database. If there is a system failure , the transaction log might be required to bring your database back to a consistent state.This guide provides information about the physical and logical architecture of the transaction log.

Transaction Log Logical Architecture

• The SQL Server transaction log operates logically.
• Each log record is identified by a log sequence number (LSN).
• Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it.
• Log records are stored in a serial sequence as they are created.
• Each log record contains the ID of the transaction that it belongs to
• For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Transaction's	LSN
trasaction 1	605679000023574000000
trasaction 2	605679000023575000000
trasaction 3	605679000023576000000
trasaction 4	605679000023577000000

Many types of operations are recorded in the transaction log. These operations include:

• The start and end of each transaction.
• 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
• Every extent and page allocation or deallocation.
• Creating or dropping a table or index.

Transaction Log Physical Architecture

• The transaction log in a database maps over one or more physical files. 
• There must be at least one log file for each database.
• 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 size or number of virtual log files cannot be configured or set by administrators.
• 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. 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. 
  
  
  

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