Readers bump into this blog because it tries to simplify some of the most complex topics into a digestible format. I am a strong believer of this and try to bring some of these topics from time-to-time. Even I find some of these topics confusing, if I can understand them in simple words – will they reach the blog immediately as part of notes of my learning. This blog is no different in this regards because I have been wanting to write about TLogs for a while and the opportunity just came from. Ever since I wrote about the WAL protocol, this followup blog has been pending.
The SQL Server transaction log operates logically as if the transaction log is a 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 that is higher than the LSN of the record before it.
Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.
Log records for data modifications record either the logical operation performed or the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the 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 the log record:
- Logical operation logged:
- To roll the logical operation forward, the operation 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 afterimage is applied.
- To roll the operation back, the before image is applied.
Many types of operations are recorded in the transaction log. These operations include:
- The start and end of each transaction.
- Every data modification (insert, update, or delete). This includes changes by the system stored procedures or data definition language (DDL) statements to any table, including system tables.
- Every extent and page allocation or deallocation.
- Creating or dropping a table or index.
Rollback operations are also logged. Each transaction reserves space in the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.
The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to make a full recovery of the database. No part of the active log can ever be truncated.
I hope this was a complete dope and dump of a lot of Transaction log basics in one blog and I will try to bring up others for later blogs. Please let me know if you learnt something new today.
Reference: Pinal Dave (https://blog.sqlauthority.com)