Transaction Log Architecture

Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction. This record of transactions and their modifications supports three operations:

• 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.

  After the loss of a database, as is possible if a hard drive fails on a server that does not have RAID drives, you can restore the database to the point of failure. You first restore the last full or differential database backup, and then restore the sequence of transaction log backups to the point of failure. As you restore each log backup, SQL Server reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, SQL Server then uses the log information to roll back all transactions that were not complete at that point.

The characteristics of the SQL Server 2000 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 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 on several files. The files can be defined to autogrow as required. 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.