SQL Server Log Architecture

The log file is not just that stores information, this is mainly responsible for data consistency. This file stores all the transactions that modify/add data in the database.       

Log files has VLF (Virtual Log Files) in it instead of pages unlike data files. VLF’s may not be of the same size, we can manually change the size but i recommend to leave that tricky job to SQL server itself. Transactions are written into log files into VLF’s splitting the transaction if required.

Each transaction will be written in sequential order and will be given a unique identification number call LSN (Log Sequential Number). LSN is a three-part unique number assigned to each transaction which helps in determining where exactly particular transaction is located in the log file.

This LSN plays a major role in logging operations like committing the transaction, rollbacking the transaction, taking a backup, restoring the database and many others.                              

VLF will have 4 different states and log file operates in these four cycles in a cyclic manner.

Active: – This portion of VLF will have one/ more active transactions executing / waiting to be executed.

Recoverable: – This portion of log has transactions executed but data not cleared from log, it may be waiting for clean-up or backup to happen

Passive: – This is also called reusable log in which log is cleaned up, this space of log can be reused.

Unused: – This is a free part in the log file which is ready to be used by new transactions.

SQL Server Log Architecture

 Log Buffer: This is a small storage are in SQL server buffer pool with the size of 60Kb which stores the transactions in it before writing them to physical log file this is to optimising the performance of SQL server by writing into disk more efficiently.

Leave a Reply