Transaction Log Logical Architecture
The Microsoft® SQL Server™ 2000 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.
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.
- To roll the logical operation forward, it is performed again.
- 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.
- To roll the operation forward, the after image is applied.
Many 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.
Rollback statements are also logged. Each transaction reserves space on the transaction log to ensure enough log space exists to support a rollback if an error is encountered. This reserve space is freed when the transaction completes. The amount of space reserved depends on the operations performed in the transaction, but is generally equal to the amount of space used to log each operation.