Understanding Transacation Log and Recovery Model

Every SQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. This guide provides information about the architecture of the transaction log and its various different uses in SQL Server.

SQL Server databases are stored on disk in two files: a data file and a log file.

SQL Server Data File(.mdf)

  • All data lives in data file and each file belongs to a FileGroup (default Primary).
  • File access pattern is random in nature for both Read and Writes and generally multiple files are used.
  • Changes occur here AFTER changes have occurred in LOG because of WAL mechanism(discussed in detail below).

SQL Server Transaction Log File(.ldf)

  • Captures changes that occur in the database such as
    • Changes to Data: INSERT, UPDATE, DELETE
    • Changes to Metadata: CREATE, ALTER, DROP
    • Changes to Structures: Allocations, Splits, File Growth etc
    • Other Bookkeeping Information: CHECKPOINT etc
  • Any change is always captured here first and later persisted in the data files.
  • File access pattern is sequential for Writes and mostly sequential for Reads, so mostly one file is used.
  • Implements Atomicity and Durability ACID property.

Transaction Log Architecture

Virtual Log Files (VLFs)

  • Entire Log Is divided up into chunks called Virtual Log Files (VLFs).
  • Newly created VLFs are inactive and unused, these can be used for logging. (except in case of new database, first VLF is always active).
  • Active VLF cannot be reused until it is made inactive by Log Clearing (Log Truncation by Checkpoint or Log Backup). There must be at least one active VLF in the transaction log.
  • VLF are used as unit of Growth, Shrinking and Truncation.

VLF Status:

  • Active or not backed up (or not truncated) (Status = 2)
  • Inactive or unused or backed up (or truncated)  (Status = 0)

VLF Sequence Number

  • Each VLF has a sequence number, which uniquely identifies it, sequence numbers increase by one each time the next VLF is made active
  • The start of the active portion of the T-log begins with the VLF that has the lowest sequence number and is still active.

Log Blocks

  • It is the unit of physical commit to a transaction log file, it can vary in size from 512 bytes to 60K
  • It is stored as a “Log Buffer” in memory in buffer pool
  • The log block size is set when one of the following occurs:
    • A transaction generates a log record to commit a transaction
    • The log block size reaches 60K without a transaction committing
    • Hence it is always more efficient to have larger (closer to 60K) transactions as small transaction can decrease performance.
  • Each log block contains log records which are stored in order and there can be log records from multiple transaction in single log block.

LSN – Log Sequence Number

  • Unique identifier for each log record in entire transaction log and are ever increasing. (LSN = <VLF sequence no>: <Log block no> : <Log record no> )
  • Each data-file page has an LSN in its page header that identifies the most recent log record whose change is reflected on the page, this is required in Analysis phase in Recovery to determine if REDO or UNDO is required (discussed in recovery process in next section in this guide).

Bringing a SQL Database Online(Recovery Process)

A database must go through a series of operations to put the database in a consistent state and this entire process is called as Recovery
Recovery must always happen when:

  • A database is brought online
  • A database is attached
  • A database is restored
  • When database fail-over occurs in HADR

Phases of recovery:

  • Analysis Phase: Read the log and compare LSNs of log records  for committed or uncommitted transactions with the LSNs in data file pages, to determine whether there’s any redo or undo to be done of those particular records.
  • Redo Phase: Replays committed but un-reflected transactions.
  • Undo Phase: Roll back active transactions at time of crash.

What is transaction Log used for?

1) Write Ahead Logging: SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk i.e. all changes must be permanently recorded in log before being written to the data file. This maintains the atomic and durability ACID properties for a transaction.

Atomic Property A change occurs in entirety or not performed at all.

  • (Undo – Rollback) If an application issues a ROLLBACK statement, or if the Database Engine detects an error in executing a transaction, or if a server fails, the log records are used to roll back the modifications made by every incomplete transaction to make sure the integrity of the database is preserved.

Durability Property A change must be hardened to stable media.

  • (Redo – Replay) If a server fails, the databases may be left in a state where some modifications were written to log but were never written to the data files, so when an instance of SQL Server is restarted, it runs a recovery and every modification recorded in the log which may not have been written to the data files is rolled forward to make sure the integrity of the database is preserved.
  • Checkpoint Ensures the REDO portion of recovery stays to a minimum. It writes modified (dirty) buffer pages to disk (data file). It writes ALL dirty pages to disk irrespective of whether transaction is committed or not. It does not remove pages from the buffer pool, Lazy Writer does this. It is also responsible for log truncation in Simple Recovery Model.

2) Log Backup/Restore: The main purpose of the SQL Server Transaction Log is to ensure that your database can be brought back to a consistent state in case of a system failure. This can be achieved by taking LOG  backup which archives log records that are no longer needed for REDO and UNDO to backup file. This can be used in conjunction with FULL backup to restore to a point in time.

3) (HADR) high availability and disaster recovery solutions (Log is used in all HADR solutions).

  • Log shipping (DR): T-Logs are backed up on Primary server and transferred to Secondary server and is restored here (sends both committed and uncommitted transactions).
  • Transactional Replication (HA): Scans committed log records in the Publication database and converts them into logical operations which are then sent to Subscription databases.
  • Database Mirroring (HA): Sends committed log records continuously from the Principal database to Mirror database

Database Recovery Models

SQL Server logs information about each and every transaction made, into the transaction log before the changes are written to the database. The amount of information logged depends on the recovery model of your database. SQL Server offers 3 different recovery models: Full, Bulk Logged and Simple.

FULL

  • All operations are fully logged.
  • Log space can only be reused after log backups.
  • Checkpoint flushes dirty pages to data files but Log is truncated at log backup.
  • Can recover to an arbitrary point in time using Tail of the log backup.

BULK LOGGED

  • Some operations are minimally logged.
  • Log space can only be reused after log backups.
  • Checkpoint flushes dirty pages to data files but Log is truncated at log backup.
  • Point in time recovery not possible as we cannot take Tail of the log backup.
  • This recovery model is designed as a temporary mode for faster data loading.

SIMPLE

  • Operations are logged for the duration of the transaction, once transaction commits log space is available for reuse.
  • No log backups required, automatically reclaims log space.
  • Checkpoint truncates Log and flushes dirty pages to data files.
  • Changes since the most recent database backup are unprotected.

(Neither Log Backups nor Checkpoint shrinks the transaction log)