MS SQL Server Transaction Log Architecture

SQL Server Transaction Log files are the inherent part of every Microsoft SQL Server files, Also known as database log, transaction journal, binary log file etc. A transaction log contains history of all the activities executed in database. From disaster recovery point of view, the transaction log file plays an important role. This transaction log guarantees ACID properties of MS SQL server at the time of system crash or failure. The different types of log files include Update Log Record, Compensation Log Record, Commit Record, Abort Record, Checkpoint Record and Completion Record. Let’s see logical and physical architecture of the transaction log files in detailed.

Characteristics of Transaction Log file

  • Log files are implemented as a separately from other files in the database.
  • Simple, fast, and robust since it is managed separately from buffer cache.
  • The log files can be implemented over several files physically separated. This can reduce the potential of space run out in transaction log file, moreover reduces administrative overhead.
  • Option to reuse the disk space within the transaction log files

Physical Transaction Log Architecture

A transaction log file ensures the data integrity of the database at the time of data recovery after a system failure. By analyzing the physical architecture of transaction file one can effectively manage log file to improve the performance. Transaction Log file is a collection of log records in string format. This file maps over one or more physical files from the disks. The physical log files are internally divided into several virtual log files. These Virtual files are dynamically created which does not have any fixed size. Each Physical log files maintain random number of virtual log files.

If the virtual logs grow to large size, this may cause the database performance to slow down. Assigning a value close to the final size required can improve the performance largely. This is the only time a transaction log file affects the performance of the database server.

Log architecture

Transaction log file is a recursive file where the last virtual file points to first file at the start of the physical file. A new log record is appended at the end of the previous log and is expanded towards the end of physical log. Log truncation is the process of freeing any virtual log files whose records sequence number appears before the minimum recovery time stamp. When the end of the Physical file is reached, the logical files are mapped back to the start of the physical file. A transaction file is illustrated below in the figure for reference.

Log Architecture

The looping is carried out endlessly; the old records are removed in order to create room for new virtual log records. This looping is carried out until the available disk space is finished. If multiple physical files exist, then logical logs will be traversed through the entire physical logs before it wrapped back to the first of the first physical file.

Logical Transaction Log Architecture

The logical architecture of the transaction log files of MS SQL server is very simple to explore. Logically, Transaction log file is a string of log records. A log sequence number (LSN) is used to identify each log records uniquely. The log records are numbered in sequence for the starting record onwards. Each log record holds transaction ID and all log records associated with it. Each transaction and the associated log records are linked together in a chain using pointers to make the rollback process easy. All the data modification activities are recorded in the log file as before and after images of the database. As the name indicates before image is the copy of the database prior to the data modification whereas the after image is the image of the database after modification. Various operations that are tracked in a transaction log include the start and end of every transactions, each and every data modification activities including insert, update, etc., page allocation and deallocation details, Creation or deletion of a table or index from database, etc.

How to roll back the activities from a log file?

The steps and procedures vary from type of log records created by the DBMS. In Logical operations logged in a log file roll the logical operation forward or back and carry out the appropriate reverse operation. In case of image logged file; roll the operation forward on the after image and the operation back in case of before image.

Operations that are supported in a Transaction Log file are

  • Support of transaction replication activities.
  • Recovery of individual transactions for the database.
  • Support for standby server solutions in SQL server.
  • Recovery of all incomplete transactions that are started in the server
  • Can roll back databases, file groups, files or page forward
Bottom Line

Every MS SQL Server databases have a transaction log that keeps all transactions and other activities done on the databases. These log files are critical component in case of system failure or of any mischievous activities at the SQL Server. One should never delete or move the log file unless you have full understanding of the file contents.

This entry was posted in Database Administration, Uncategorized. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s