Transaction Log Architecture

Every Microsoft® SQL Server™ database has a transaction log that records all transactions and the modifications made by the transactions in the database. This record of transactions and their modifications serves three functions:

• Recovery of individual transactions.

  If an application issues a ROLLBACK statement, or if SQL Server 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 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 a copy 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 ensure the integrity of the database is preserved.

• Rolling a restored database forward to the point of failure.

  Each time a full or differential database backup is restored, recovery is run to roll back any incomplete transactions. After a database backup has been restored, transaction log backups can be used to roll forward all completed transactions on the log backup. This allows a database to be restored to the point at which the server failed.

SQL Server version 7.0 introduces several transaction log improvements. The characteristics of the transaction log are:

• The transaction log is not implemented as a table but as a separate file or set of files in the database. The log cache is managed separately from the buffer cache for data pages, resulting in simpler, faster, and more 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 on several files. The files can be defined to autogrow as needed. This reduces the potential of running out of space in the transaction log, while at the same time reducing administrative overhead.
• The mechanism to truncate unused parts of the log is quick and has minimal effect on transaction throughput.