• Article

Transaction Log Overview

Every SQL Server 2005 database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.

Operations Supported by the Transaction Log

The transaction log supports the following operations:

  • Recovery of individual transactions.

  • Recovery of all incomplete transactions when SQL Server is started.

  • Rolling a restored database, file, filegroup, or page forward to the point of failure.

  • Supporting transactional replication.

  • Supporting standby-server solutions.

Recovery of Individual Transactions

If an application issues a ROLLBACK statement, or if the Database Engine detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.

Recovery of all Incomplete Transactions When SQL Server Is Started

If a server that is running SQL Server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When an instance of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log which may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to make sure the integrity of the database is preserved.

Rolling a Restored Database, File, Filegroup, or Page Forward to the Point of Failure

After a hardware loss or disk failure affecting the database files, you can restore the database to the point of failure. You first restore the last full database backup and the last differential database backup, and then restore the subsequent sequence of the transaction log backups to the point of failure. As you restore each log backup, the Database Engine reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, the Database Engine then uses the log information to roll back all transactions that were not complete at that point.

Supporting Transactional Replication

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. For more information, see How Transactional Replication Works.

Supporting Standby-Server Solutions

The standby-server solutions, database mirroring, and log shipping, rely heavily on the transaction log. In a log shipping scenario, the primary server sends the active transaction log of the primary database to one or more destinations. Each secondary server restores the log to its local secondary database. For more information, see Log Shipping Overview.

In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. The principal server instance sends each log record immediately to the mirror server instance which applies the incoming log records to the mirror database, continually rolling it forward. For more information, see Database Mirroring Overview.

Transaction Log Characteristics

Following are the characteristics of the SQL Server Database Engine transaction log:

  • The transaction log is implemented as a separate file or set of files in the database. The log cache is managed separately from the buffer cache for data pages, which results in simple, fast, and robust code within the Database Engine.

  • The format of log records and pages is not constrained to follow the format of data pages.

  • The transaction log can be implemented in several files. The files can be defined to expand automatically by setting the FILEGROWTH value for the log. This reduces the potential of running out of space in the transaction log, while at the same time reducing administrative overhead. For more information, see ALTER DATABASE (Transact-SQL).

  • The mechanism to reuse the space within the log files is quick and has minimal effect on transaction throughput.