In this blog, we will learn about "Transaction log" and "how it work". First, we will mention about why Transaction log appears. For example, when we are using our computers to modify database, the electricity is shut off suddenly and your action aren't completed, maybe the are coming up . It means your changes haven't written out to disk. So how we can ensure integrity of a database when the electricity went off or the server crashes for some reason. At that time, Transaction log can help we address this issue.
What is the Transaction log?
Transaction log is an integral part of DBMS. What every database has, Transaction log is stored within the log file and is separated from the data file. SQL server uses a transaction log to keep track of the changes it has made and of which ones have been safely written out to disk. Because transaction log basically saves all database changes.
How Transaction log work
Fig 1. How transaction log operate [1]
When users change the database, it is logged in the transaction log. This help we can roll back or restore database when there are failure happening to avoid data corruption. For example, you are inserting 3000 rows of data, suddenly the power was loss. Because the statement INSERT was used, it was written out to transaction log. And it find a failure happened, it will roll back the statement. When your server active again it will check transaction log and check-point of database to implement changes hasn't been happened. Because if one modification hasn't been completed, it has status is Active and else is INACTIVE. Based on this status to ensure that the change has been safely saved back to disk.
Structure of Transaction log
The transaction log is split up into small chunks called virtual log files [2]. When one of virtual log files is full, log will be written at the next virtual log file automatically. And when you truncate database it also means your virtual log file will be free up by new log will be overwritten in there. This process like a circle.
Fig 2. Virtual log files [2]
Summary
Transaction log is important to back up when failure happen but in some cases it also doesn't help you. Therefore, We should save backup files of database everyday, if any failures occur, database can still be safe.
Reference
[1] Tìm hiểu về Transaction Log (2021, March 13) Kiên
[2] What is the transaction log. Bray Upton
https://www.mssqltips.com/sqlservertutorial/3302/what-is-the-transaction-log/
