Transaction Logs - I

(Based on Paul S Randal articles)          

          Transaction log backups are only possible in the FULL or BULK_LOGGED recovery models. A transaction log backup contains all the transaction log records generated since the last log backup (or full backup that starts a log backup chain) and is used to allow the database to be recovered to a specific point in time (usually the time right before a disaster strikes). This means they are incremental, unlike differential backups, which are cumulative. Since these are incremental, if you want to restore the database to a particular point in time, you need to have all the transaction log records necessary to replay database changes up to that point in time. These are contained in the log backup chain. A log backup chain is an unbroken series of log backups that contain all the transaction log records necessary to recover a database to a point in time. A chain starts with a full database backup, and continues until something breaks the chain, thus preventing more log backups being taken until another full (or differential) backup is taken.

               Operations that break the log backup chain include switching to the SIMPLE recovery model, reverting from a database snapshot, and forcibly clearing the log using the WITH NO_LOG or TRUNCATE_ONLY options (which are not available in SQL Server 2008). It is inadvisable to break the log backup chain, as it forces another (potentially large) full backup to be taken. Log backups are also required to help manage the size of the transaction log. In the FULL or BULK_LOGGED recovery models, the log will not clear until a log backup has been performed. If the log cannot clear, the log will grow until it runs out of space.

               There is a special case in logging that improves performance by allowing some operations to run as minimally-logged operations, where only the page allocations are logged, not the actual insertion of data. This can improve the performance of operations such as bulk loads and index rebuilds. The first log backup following a minimally-logged operation will also contain some data.

               Crash recovery is only possible if the transaction log is intact. As well as the space occupied by the log records themselves, a transaction will also reserve space in the transaction log for any potential log records required if the transaction were to be canceled and required to roll back. This accounts for the behavior you may observe where, say, a transaction that updates 50MB of data in the database may actually require 100MB of transaction log space.

               When a new database is created, the transaction log is essentially empty. As transactions occur, log records are written sequentially to the transaction log, which means there is no performance gain from creating multiple transaction log files. The transaction log will use each log file in turn. Log records for concurrent transactions can be interspersed in the transaction log. Remember that log records for a single transaction are linked by their LSNs, so there is no need for all log records for a transaction to be grouped together in the log. LSNs can almost be thought of as a timestamp.