As we know transaction
log file is mandatory database file for a database. Every database must have at
least one transaction log file. SQL Server uses transaction log file to capture
log records that guarantee the data integrity of the database and for data
recovery.
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 such that if LSN2 is greater than LSN1, the change described by the log
record referred to by LSN2 occurred after the change described by the log
record LSN1. 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 they record 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 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 after image is applied.
- To
roll the operation back, the before image is applied.
There are many types of operations recorded in Tlog files. These
operations include:
1. The start and end of
each transaction.
2.
Every data modification (insert, update, or delete). This includes changes
by system stored procedures or data definition language (DDL) statements to any
table, including system tables.
3. Every extent and page
allocation or deallocation.
4. Creating or dropping
a table or index.
5. Rollback operations. Each transaction reserves space on 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. 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 do a full
recovery of the database. You can not truncate any part of active log.
Rollback operations are also logged.
Each transaction reserves space on 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, active log, or tail of the log. This is the section of the log required to a full recovery of the database. No part of the active log can ever be truncated. The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN). For more information on operations supported by the transaction log, Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.
No comments:
Post a Comment