How transaction logging works

Following is a general example of transaction logging from both the administrator's and the employees' points of view.

The administrator enables transaction logging for all the databases on the servers. The administrator chooses the Archived logging style so that there is plenty of room for the transaction logs; uses a separate, mirrored device for safe and speedy storage of the transaction logs; and installs a backup utility to recover from media failures and any resulting corrupted databases.

The administrator backs up the transaction logs daily. This procedure doesn't take long because the administrator is backing up only the changes, rather than doing a full backup of all the databases on the server.

When the server crashes, it's down, but not for long. As the administrator restarts the server, it replays all the changes from the transaction logs to the databases. The server is soon back in business.

A few days later, there's a media failure. The administrator restores the corrupted databases from the most recent weekly backup and replays the changes.

The employees who use the databases do not notice any difference in how they do their work. They might notice, however, that servers are up and running more often and that there is less down time.

How changes are made to the database

Transaction logging posts all the database transactions to the log file, without waiting for the transaction to commit to disk. After being posted to the log file, the change is considered successful. The physical write process can wait until the server is less busy or occur at periodic intervals. The changes are written to disk in a batch.

Databases are cached in memory while they are open. The writes to the database happen to the in-memory copy of the database. They are then immediately sent to the transaction logs. Later, the memory-cached version of the database is posted to disk, updating the databases. Since the transaction log is sequential, there is no seek time, and only enough information is written to the logs to redo (or undo if necessary) the operation. In many cases, this is less information than the database write to disk.

If the database is not yet completely written to disk and you open it, you are opening the memory-cached version. If the server crashes before the version on disk has been updated with the changes, restarting the server applies the logs to the database during restart.