The transaction log is a separate file from the database file. It stores all changes to the database. Inserts, updates, deletes, commits, rollbacks, and database schema changes are all logged. The transaction log is also called the forward log or the redo log.

The transaction log is a key component of backup and recovery, and is also essential for data replication using SQL Remote, the Replication Agent, or database mirroring.

By default, all databases use transaction logs. Using a transaction log is optional, but you should always use a transaction log unless you have a specific reason not to. Running a database with a transaction log provides much greater protection against failure, better performance, and the ability to replicate data.

It is recommended that you store the database files and the transaction log on separate disks on the computer. If the dbspace(s) and the transaction log are on the same disk, and a disk failure occurs, everything will be lost. However, if the database and transaction log are stored on different disks, then most, if not all, of the data can be recovered because you will still have either the full database or the transaction log (from which the database can be recovered) in the event of a disk failure.

See Protecting against media failure on the database file.

When changes are forced to disk

Like the database file, the transaction log is organized into pages: fixed size areas of memory. When a change is recorded in the transaction log, it is made to a page in memory. The change is forced to disk when the earlier of the following happens:

In this way, completed transactions are guaranteed to be stored on disk, while performance is improved by avoiding a write to the disk on every operation.

Configuration options are available to allow advanced users to tune the precise behavior of the transaction log. See cooperative_commits option [database] and delayed_commits option [database].

Transaction log mirrors

A transaction log mirror is an identical copy of the transaction log, maintained at the same time as the transaction log. If a database has a mirrored transaction log, every database change is written to both the transaction log and the transaction log mirror. By default, databases do not have transaction log mirrors.

A transaction log mirror provides extra protection for critical data. It enables complete data recovery in the case of media failure on the transaction log. A mirrored transaction log also enables a database server to perform automatic validation of the transaction log on database startup. See Protecting against media failure on the transaction log.