Transaction Log File in sql server

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.