Transaction Log Logical Architecture

The Microsoft® SQL Server™ version 7.0 transaction log operates logically as if it is a serial string of log records. 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 higher than the LSN of the record before it.

In SQL Server 7.0, log records for data modifications record either the logical operation performed or before and after images of the modified data. A before image is a copy of the data before the operation is performed, an after image is a copy of the data after the operation has been performed. The steps to recover an operation depend on the type of log record:

• Logical operation logged.
  • To roll the logical operation forward, it is performed again.
  • To roll the logical operation back, the reverse logical operation is performed.
• Before and after image logged.
  • To roll the operation forward, the after image is applied.
  • To roll the operation back, the before image is applied.

Many different types of operations are recorded in the transaction log, including:

• The start and end of each transaction.
• Every data modification (insert, update, or delete). This includes changes to system tables made by system stored procedures or Data Definition Language (DDL) statements.
• Every extent allocation or deallocation.
• The creation or dropping of a table or index.

Log records are stored in a serial sequence as they are created. Each log record is stamped with the ID of the transaction to which it belongs. For each transaction, all log records associated with the transaction are singly-linked in a chain using backward pointers that speed the rollback of the transaction.