When we are creating the database there two files created Data file (.mdf) and Log file (.ldf). Apart from that also we can create (.ndf) secondary database files.
Every database consists of one or
more transaction log files and one or more data files. A transaction log stores
information about database transactions and all of the data modifications made
in each session. Every time the data is modified, SQL Server stores enough
information in the transaction log to undo (roll back) or redo (replay) this
action, which allows SQL Server to recover the database to a transactional
consistent state in the event of an unexpected failure or crash.
A transaction log, also called a T-log,
is a running list of transactions that change either the data in the database
or the structure of the database. Each database modification is a transaction;
as changes are made to a database, log records are added to the log file.
Transaction log can be used to minimize data loss if the disaster or a database
corruption occurs. In other word we can say that Transaction log is a list of
records for all Microsoft SQL database changes.
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.
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 system stored procedures or data definition language (DDL) statements to any table, including system tables
Ø Every extent and page allocation or de allocation.
Ø Creating or dropping a table or index.
Transaction Log Physical Architecture
Ø The transaction log in a database maps over one or more physical files.
Ø There must be at least one log file for each database.
Ø The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file.
Ø The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files.
Ø The size or number of virtual log files cannot be configured or set by administrators.
For example, consider a database with one physical log file divided into four virtual log files. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log and expand toward the end of the physical log. The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback.
The transaction log in the
example database would look similar to the one in the following
illustration.
When the end of the logical log reaches
the end of the physical log file, the new log records wrap around to the start
of the physical log file.
This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint.
