Guide to SQL Server Transaction Log Architecture

Guide To SQL Server Transaction Log Architecture

Guide to SQL Server Transaction Log Architecture

All SQL Server databases like those offered by Microsoft maintain a transaction log. It aims to record all the database modifications made on each transaction. For effective database management, this transaction log is a critical component. Which comes into use when there is a system failure. This log is also essential in ensuring small businesses with data backup. A transaction log is a must to bring the database back to the consistent state in system failure. This is a brief overview of the architecture (physical and logical) of an ideal transaction log. Understanding this will improve your ability to manage the transaction logs better.


The Logical Architecture of SQL Server

Transaction logs of SQL Server operate logically as a string of log records. Log sequence number or LSN identifies individual log records. Every new log record is written on to the logical end with a unique LSN. It then succeeds the previous log sequence number. Log records gets stored in a serial sequence, i.e., LSN1, LSN2, LSN3 and more. LsN2 log records describes the changes after a modification in LSN1. Each of these log records will contain the transaction ID belong to the same. All the log records related to the transaction get individually linked in a chain. This chain forms by using backward pointers. That speeds up the transaction rollback when needed.
For data modification, the log records follow the logical operation performed. It can also record the before and after images of modified data. Before image remains a copy of the data before a procedure is completed, the after image is the post-operation copy.
Below are the steps to recover an operation based on the log record type:
  • A logical operation gest logged
  • To roll it forward, one needs to perform the operation again. 
  • To roll it back, one performs a reverse logical operation
  • Logging of before & after images
  • To roll operation forward, one must apply an afterimage
  • To roll an operation back, before the image applies
  • Many of these operations go into the transaction log. 

Transaction Log Operations of SQL Server

Every modification of data as insert, delete, or update includes changes by the system stored procedures. The DDL (data definition language) statements to the tables, including that of the system tables. The rollback operations also get logged. Every transaction involved will reserve space on the transaction log. That is to ensure that there is enough log space to support the rollback. An explicit rollback statement happens if there is an error occurring. Space reserved for these rollbacks depends on operations in the. However, generally, the space allocated is equal to the space used to log each operation. Once the transaction is complete, this space reserved frees up.
The particular log file section from the first log record, is known as the log’s active log or tail. It is present for a successful database-wide rollback stretching to the last-written record. Complete database recovery needs this particular section. You cannot truncate any part of the active log. LSN of the very first log is called minimum recovery LSN  or MinLSN. The log backups and differentials advance the database restored to a later time. This further corresponds to a higher LSN if you want to get help in remote database administration. It can provide add-on help for areas like transaction log management, explore the services of RemoteDBA.com.

The Physical Architecture Of A Transaction Log

Transaction log in a given database maps over one or many physical files. The log file is a log record string. Physical file set log record sequence, which implements the transaction log. There should be at least a single log for each given database.
The SQL Server Database Engine may internally divide each of the physical log files into many virtual log files (VLF). The virtual log files may not have any fixed size.  Also, there is no fixed count of virtual log files for the physical log. Database engines tend to choose the virtual log file size dynamically while creating a new log file or extending the same. Database Engine will try to maintain as small in number virtual files as possible. The size of virtual files vary after a log file extends the sum of sizes of all existing logs and the size of a new file increment. You can now set the number or size of virtual log files in the windows server essentials backup

Transaction Logs In Bigger Sizes

If the given log files tend to grow to a bigger size by summing up all the small increments, there may be many log files virtually. This may further slow down the slow database startup and also may back up and restore the operations. Additionally, if the log files are set to a bigger size with just one or a few increments, they may have a few large virtual log files existing. To get more info on estimating the required size, you may check the Recommendations section in the support files. That will also set the auto-grow setting of the transaction logs.
It is a must that you first assign the log files to a size value closer to the finally required size. You can do this by doing increments to achieve an optimal VLF distribution. That also gives a larger value for growth increment. The size value set by the SIZE argument of the ALTER DATABASE will be the initial size for your log file. A growth increment value or the auto grow value will be set by the FILEGROWTH argument of the ALTER DATABASE argument. That will be the amount of space that adds to a file each time when requiring bigger space.
Wrap Up
The transaction log may be a wrap-around file. You may consider a database that only has one physical log file that divides into four VLFs. With the creation of the new database,  the log files also being at the initiation of a physical log file. Further, the log records adds end of it, as we described above in a logical series, and it expandstowards the end of the physical log. The log truncation may free up any virtual logs. That is where the records appear in front of the least recovery log sequence or MinLSN. This is the sequence number of the oldest log record, which complete rollback requires
You need fair understanding of both these types of transaction log architectures. It wil help you have better management in logs and execute restorations when there is a failure.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *