Learn about Transaction Log

Hello everyone, usually when you find quite ‘detailed’ topics about SQL, you are probably familiar with what the concept of a database is. Today I will share about an aspect that almost when we work with the database management system (namely SQL SERVER), it will be ignored and even unknown to it, that is Transactions. Log .

Before getting to the concept, let’s go over the reason that leads us to the Transaction Log. The story is as follows, when you are doing good manipulations with the database, your hard drive is burnt, or the System file is corrupted, and more often, you delete / update by mistake. At that time, the first thing you need to do is search for the most recently backed up database to revert the data, but assuming it has been a day since you haven’t backed up your data, you really want to get your data back up. it took you all day to create. So what to do, let’s find out more with me ^^

1. What is Transaction log?

It is a series of records that store information data updates performed to the database, operating underneath a DBMS and of course it is extremely important. Normally, our database will consist of two components, 1 is the data file, used to hold the information to form the database (information about the database structure, data, …), 2 is Log file (Transaction Log) will contain the records of updating data into database such as Insert, Update, Delete. That means all operations that make changes to the database data are stored in the Log File.

>>> Why should it be split into 2 parts when it is possible to merge them into 1 for compact?

The reason is processing efficiency, because the data file is a complex structure, and to process (analyze the structure, divide the pages, create new extents, store to hard drives) a complex structure. trash will be very time consuming. Now we will divide the input data into 2 parts, the value of the data that needs to be stored will be put into the buffer of RAM instead of being saved directly to the hard disk , each operation with data will be immediately entered 1 Another file (Transaction Log). Since the Transaction Log stores data sequentially and in a simple structure , the processing is very fast.

>>> When will the data in the buffer be stored in the Data File?

That’s when a Checkpoint happens. Checkpoints can be executed by the user or by SQL Server automatically based on the database settings. At that time, the data in the buffer will be saved to the Data File on the hard disk. At the same time, information about the Checkpoint will be recorded to the Transaction Log.

>>> What happens if the computer is shut down suddenly (or the hard disk burns down) while the data in the buffer has not been written to the Data File?

This is where the Transaction Log records come into play. In this situation, when starting up, SQL Server will read information from the Transaction Log (the records generated after the most recent Checkpoint) to recover the unsaved data to the Data File. In this process, SQL Server will use redo (roll-forward) and undo (roll-back) operations to ensure transaction consistency.

2. Transaction Log Structure

Transaction Log data can be understood as the records stored as shown above. Each modification of the data is recorded as a series of records in the Transaction Log. Each record is numbered Log Sequence Number (LSN) and stored in the Virtual Log File. The number and size of the Virtual Log File is determined by the SQL Server Database Engine.

In the Transaction Log, the records required for Full Recovery of the Database are called Active Logs. When the information contained in the Transaction Log records is written to the Data File (with the Recovery model is SIMPLE), or when the Transaction Log is backed up (with the Recovery model FULL / BULK-LOGGED) the Active Logs become Inactive Log. . The storage area containing the Inactive Logs can then be used to store new Active Logs. This process is called Log Truncation.

In essence, the Log Truncation process does not delete the data, so it does not reduce the Log File size. Here, SQL Server only marks records that are no longer in use and can therefore be overwritten with new records. Therefore, the Transaction Log does not need to increase the size of the new record.

The records are recorded in the Transaction Log according to the circular mechanism. That is, Active Logs are recorded sequentially from the end of the file. If the file is saved to the end of the file that the header storage area can use (due to the Log Truncation occurring), the new records will be overwritten in this area.

So if writing to the end of the file and the record at the beginning of the file is still Active Log, what happens? At that time, SQL Server will increase the size of the Log File so that it can add a new Active Log (the size of each increment is set in the File Growth property of the Log File). If you do not limit the size, the Log File will continue to increase until the header storage area is free, or the disk becomes full. Of course, when the drive is full, the Transaction Log will not be able to write any more data so the database will stop working.

Log Truncation will be performed when one of the following 2 situations occurs:

• If the Recovery model is SIMPLE : When a Checkpoint occurs, the data currently in the buffer is saved to the Data File. Therefore, the corresponding records will become Inactive Log. New records can be overwritten in this area.
• If the Recovery model is FULL or BULK-LOGGED: With these 2 Recovery models, even if Checkpoint occurs, SQL Server still does not perform Log Truncation. Therefore, the Active Logs do not become Inactive Log for the storage area to be recovered. Instead, you must backup Transaction Log with the BACKUP LOG command. Only when backed up do the Active Logs become Inactive Log. Thanks to that, the new storage area is recovered.

Depending on the Recovery model set up for the Database, you need to have a reasonable Transaction Log management plan to avoid the case of Transaction Log capacity increasing, filling the disk, making the Database unable to function. If the Database’s Recovery model is SIMPLE, you don’t need to pay much attention to Transaction Log management. Because then SQL Server will perform Checkpoint to write data to Data File and Log Truncation process. In case the Recovery model is FULL or BULK-LOGGED, you need to back up the Transaction Log periodically for the Log Truncation to take place.

3. Make a backup of the Transaction Log

Backup Transaction Log is a necessary operation that helps you in two aspects:

1. Make the Log Truncation process happen. As a result, the Log File size does not have to be increased to accommodate new records.

2. Make a backup of Transacton Log so that you can recover when Database is damaged or lost.

With Transaction Log backups, you can restore data to any desired time (Point-in-Time Recovery). Especially, when the Database is corrupted or the Data File is corrupted but the Log File is still usable, you can perform Tail-Log Backup to back up the existing Transaction Logs. Combined with previous Transaction Log backups, you can restore the Database to the point in time right before the problem occurred.

To backup Transaction Log, use the command BACKUP LOG as follows:

Keep in mind, Transaction Log backups themselves cannot help restore the database. First, you need to restore the Database using a Full (and Differential) backup with the NORECOVERY option. You can then restore from Transaction Log backups. Additionally, the Transaction Log backups restored must be consecutive.

Document source:

http://backupacademy.zbackup.vn/sql-server/sao-luu-transaction-log-trong-sql-server/