Understanding SQL Server Transaction Log Architecture


Transaction log is a log that maintains the record of every transactions that has occurred. It involves data modifications, rollback modifications and database modifications. It is a major part of database’s architecture. It records every transaction in an order by utilizing LSN (Log Sequence Number). Every transaction is appended to physical log file and utilizes LSN, which is of higher value than the previous LSN. It is mainly used by SQL engine to confirm the data integrity. In the following section, we will discuss an architecture of transaction log.

Logical Architecture Of Transaction log

Transaction logs works as if they are saved with string of log records. Every record is recognized with the help of Log Sequence Number. It has sufficient space to maintain successful rollback, either by the occurrence of an error within database or by an explicit action for rollback request. This amount of space can be varied but it mainly copies the amount of space, which is used to save logged operation. It is loaded into virtual log that are not controllable.

There are some steps, which are used to recover an operation. It depends on type of log record such as for data modifications or before and after images modification of data.

  • Logical operation is logged
    1. The operation is performed again if user needs to roll the logical operation forward.
    2. The reverse logical operation is performed that helps to roll the logical operation back.
  • Before and after of image logged
    1. When the image is applied after, user needs to roll the forward operation
    2. If the image is applied before, then user needs to roll the back operation.

There are various types of operations, which are recorded in transaction log as mentioned:

  • The beginning and end of every transaction
  • Each modification in data, i.e. update, delete, or insert includes the DDL (data definition language) statements of any table by including table system.
  • Each modification in data, i.e. update, delete, or insert includes the DDL (data definition language) statements of any table by including table system.
  • Each extend and page de-allocation or allocation
  • Dropping or creating an index or table.

The section of log files from the first log records, which should exist for a proper database-wide rollback to the last-written log records is known as active part of log. In this, section full recovery of database is required.

Physical Architecture Of Transaction Log

Transaction log in a database maps multiple physical files. Physically, the order of log records is saved proficiently in the usual physical files, which implement the transaction log. The SQL Server Database Engine divides internally every physical file of log into various virtual log files. Virtual log files do not have any fix size as well as no fixation of number of files present for physical log file. While extending or creating log files, the database engine selects dynamically the size of virtual log files. Virtual log files number or size are not configured or not even set by administrators. The virtual files only affect the system when its size and increment growth values are defined. If the log files grow to large size due to many small increments, then it will have many virtual log files. It results in slow down of database startup operations as well as backup and restores operations.

Checkpoints Effect on Transaction Logs Architecture

Checkpoints remove the dirty data pages from the current database to disk of buffer cache. It reduces the active portion of log, which is used to process at the time of full recovery of database. At the time of full data recovery, there are various types of actions performed as mentioned:

  • Before the system stops or rolls forward, there are log records of modifications should not be removed.
  • The modifications that are associated through incomplete transaction are rolled back.

Checkpoint Operations

Checkpoint executes the mentioned way in database:

  • Note the record of log file by marking the start of checkpoint
  • Saves the data recorded for checkpoint in a chain.
  • If the simple recovery model is used to mark for the reuse of the space, which leads the MinLSN.
  • Note the record marking to end log file of checkpoint
  • Note the LSN of start of the chain

NOTE: Checkpoints contains the record of active transactions, which have modified the database.

Reasons for Occurrence of Checkpoints

Checkpoints can occur in some situations as discussed below:

  • It occurs in the present database for the connection when A CHECKPOINT statement is executed.
  • When negligible logged operations are performed in the database such as Bulk-Logged recovery model is used to perform an operation on database.
  • SQL Server is stopped with a SHUTDOWN statement or by simply stopping the MSSQLSERVER service. This action may cause a checkpoint in every database in the case of SQL Server.

Write-Ahead Transaction Log

There is a write-ahead log (WAL) in SQL Server, which gives the guarantee of no modification of data before the associated log record is written to disk. It helps to maintain the ACID properties of transactions. As the SQL Server keeps the buffer cache, on which it reads data pages at time when data is saved. The modifications on data are not done directly to disk, but are made to replica of the page in buffer cache. A page, which is modified but not written to disk, is known as dirty pages.

Conclusion

As discussed above the transaction logs play an essential feature in recovering and maintaining the database. If the records are set and maintained properly then, it will help in providing the additional backup support, which the user need without affecting system’s performance. Even it also plays an integral file in recovering database to a point in time.