Skip to main content
Our Tech Ideas

SQL Server Transaction Log Architecture

Transaction Log Architecture

SQL Server uses LSN (Log Sequential Number) in identifying the transaction. Each and every transaction that comes to the log file will associate with an LSN number. Roll forward and the rollback will be done internally using these LSN numbers only.

WAL (Write Ahead Logging)

Before committing in MDF every transaction should be written an entry in a log file is called WAL. Transactions never come to MDF directly.

Logfile divided into 2 parts.

  • Active portion (or) Physical Log
  • Inactive Portion (or) Virtual Log

Active Log Portion: whenever performs transactions it will have 3 states.

  1. Committed to the log file and waiting for the checkpoint.
  2. Failed in the middle
  3. Transactions still running

All these 3 states’ transactions will be in Active Portion of Log file. When checkpoint runs committed transactions make a copy in inactive portion and moves to MDF.

Inactive Log

SQL Server maintains fully committed transactions in these Inactive portions. This portion only used for taking the backup of the log. Whenever we take log backup it copies the inactive portion and truncates the inactive portions.

We have 2 types of backup for log portions.

Full backup takes the backup of MDF and Active log portion log backup takes the backup of an inactive log portion. This portion we call as a virtual log. SQL Server does not use these records that’s why it calls as Inactive virtual logs.

SQL Server Log Architecture

Inactive portion further divided into more virtual logs we have a property called log reusability. Log backup copy inactive portion to a file and truncates the log data. The same space can be used multiple times called log reusability concept.

The transaction log is a cyclic process of writing log records into virtual log file by the SQL server. Whenever one virtual log is filled up it will go to the next virtual log. If all virtual logs files are filled up the inactive portion will grow further and creates more virtual logs, till we have log space allocated. If it cannot grow further it will throw an error “ Transaction log for database is full and the transaction will fail”.

The only way to clear the inactive virtual log is to take log backup released logs. After truncation, this space will be released. The backup will not active portion.

Advantages of T-Log

  1. IT provides Transactional consistency.
  2. It provides transactional recoverability
  3. It provides log reusability.

Enhancement in SQL Server 2012 (Indirect checkpoint)

A checkpoint occurs automatically based on work hard (or) by a certain operation internally. We can set SQL server level recovery interval to run checkpoint by using SP_configure (or) manually issue a checkpoint using a checkpoint T-SQL command.

In SQL Server 2012, we have enhancement on checkpoint at database level TARGET T- RECOVERY-TIME option. Increasing time of recovery to seconds (or) minutes.

Alter database sql test 2012 set Target-recovery-time = 5 seconds.

Here every 5 seconds checkpoint will occur.

#Ref. Ram Mohan & Rama Krishna notes

Below topics further included in this series