SQL Server Transaction Architecture

SQL Server uses LSN (Log Sequence Number( in identifying the transaction. Each and every transactions that comes to log file will associate with a LSN number. Roll forward and roll back will be done internally using these LSN numbers only.

Write Ahead Logging(WAL):

Before commit in mdf every transaction should be written a entry in log file is called Write Ahead Logging(WAL). Transactions never comes to mdf directly.

Log file divided into two parts:

Active Portion or Physical Log
Inactive Portion or Virtual Log

Active Log Portion:

When user performs transactions, it will have three states
1. Committed in log dile and waiting for checkpoint
2. Failed in the middle
3. Transactions still running.

All these 3 state 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 Portion:

SQL Server maintains fully committed transactions in this Inactive Log Portion. This protion only used for taking the backup of log. Whenever we take log backup, it copies the inactive portion and truncates the inactive portion. We have two types of backup for log portions.
Full backup takes backup of mdf and Active Log Portion.
Log backup takes backup of inactive log portion.

Advantages of Transaction Log:
It provides transactional consistency.
It provides Transactional recoverability.
It provides log re-usability.

Powered by k2schools