The transaction log

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 synchronization using MobiLink or for data replication using SQL Remote.

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 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 is lost. However, if the database and transaction log are stored on different disks, then most, if not all, the data can be recovered in the event of a disk failure because you have the full database or the transaction log (from which the database can be recovered). See Media failure.

The timestamp of a database or transaction log file is updated only when the file grows or when it is closed. If database operations cause the transaction log file to grow without the database file growing, the timestamp of the transaction log file is more recent than the timestamp of the database file. If the database is shut down, the transaction log file and the database timestamps are updated.

Caution

The database file and the transaction log file must be located on the same physical computer as the database server or accessed via a SAN or iSCSI configuration. Database files and transaction log files located on a remote network directory can lead to poor performance, data corruption, and server instability. See [external link] http://www.sybase.com/detail?id=1034790.

 When changes are forced to disk
 See also

Transaction log mirrors
Changing the location of a transaction log
Transaction log mirrors for existing database
Transaction log size considerations
Outstanding transactions on connections
Checkpoints
Checkpoint logs