SQL Server Transaction Log Architecture and Management

                                                             Transaction Log Architecture

                             The transaction log is used to guarantee the data integrity of the database and for data recovery

      The Buffer manager will tell the transaction manager to log an update, the transaction manager then passes down to the log manager and

       transaction manager writes the change to the log. Once to committed to disk, the transaction manager will tell the buffer manager transaction is persisted - buffer manager then changes the pages in memory (a dirty page). As the change is always made to the log first, we called it a write-ahead log.

      The check point process, running roughly every minute will scan through the buffer pool and flush dirty pages to disk - then writes a time stamp to the transaction log - anything behind this can then be dropped. Once on disk the page is added to the free list. An additional process in buffer management is the lazy writer which checks through the buffer pool and ages pages. Once at 0, a page is added to the free list and can be reused.

Note the checkpoint process timing is modified. Checkpoints occur periodically based on the number of log records generated by data modifications, or when requested by a user (issue the command ‘CHECKPOINT’) or a system shutdown. When a database is recovered at startup, transactions are rolled forward (ie, changes made it to the log, but not to disk), and rolled back (uncommitted transactions are ‘undone’). The time between checkpoints is calculated to attempt to ensure that the database is recovered within the recovery interval specified by sp_configure.

Overview of Differential Backups

A differential backup records only the data that has changed since the last differential base.

Each major type of file backup can serve as the base for a series of differential backups, such as:

• • Differential database backups

  • Differential partial backups

  • Differential file backups

A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and the when differential backup is created.

A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:

• • The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files.

  • The redo phase applies the logged transactions to the data copied from

  • the backup to roll forward that data

  • to the recovery point. At this point, a database typically has uncommitted

  • transactions and is in an unusable

  • state. In that case, an undo phase is required as part of recovering the

  • database.

  • The undo phase, which is the first part of recovery, rolls back any

  • uncommitted transactions and makes

  • the database available to users.

  • After the roll back phase, subsequent backups cannot be restored