SQL Server Transaction Logs explained in plain english
 

 

Transaction logs are the most important part of the SQL Server that any DBA has to understand. The technologies like Log shipping, replication, mirroring etc. depend on transaction logs. I have seen a lot of articles on transaction log on the net, most of them talk about the structure and working of transaction log but none of the articles is simple enough for a newbie. So thought of writing an article keeping newbies in mind. Please provide your feedback and suggestions using the comments.

In this article, we shall talk about the transaction log and its working without delving deeper in the structure of the transaction logs.

Database files and Pages

Any database in SQL Server will have at least one Data file and one Log file. Data files (with extension .mdf or .ndf) store the actual table structure and the data. SQL Server stores data in the data file in blocks of 8 Kb called Pages and are the basic unit of storage for tables and indexes. This means the records in a table are stored in the form of pages.

The log file is also called as the transaction log and is what we are going to discuss in detail. Transaction Logs is an additional database file that helps in making the transactions faster and easy to recover in the event of system failure.

Why do we require transaction log

Let us understand what happens internally when data is manipulated by SQL Server upon an update/insert or delete statement.

When SQL Server performs a data manipulation (Like Insert, update, delete) in a table, SQL Server checks if the required pages are available in memory or not (physical or virtual, depends on memory allocated by the memory manager.). If the pages are not in memory, SQL Server fetches the relevant pages from disk into the memory and a copy of it is kept in memory for performing the modifications. When the pages are available in memory, the data is manipulated. For faster processing, the changes are made to the pages/data in memory and are not written to a data file in the disk immediately. At this point, the data in the memory corresponding to the records manipulated are different from those in the mdf file. If the same records are fetched again for other manipulations, SQL Server checks if the pages are in memory. Since the pages are available in memory, the latest/updated data is available for processing.

If there is a server failure at this point, the data modified in memory will be lost as it’s not written to disk or permanent storage. Hence there is a risk of losing the data if there is a failure in the system even though the transaction is committed. In order to mitigate this risk, Microsoft introduced an additional file called transaction log to keep track of changes that are happening in the database. After a transaction is committed, SQL Server immediately writes the information about what changed (like this happened, that happened… etc.) into the transaction log file (i.e. LDF file). This way, all the changes made to the pages in memory are tracked and written to Log file in the disk/permanent storage for future reference once the transaction is committed. Whenever System fails, the data in the log file can be used to recover the data modified in memory.

Restart recovery and Checkpoint

When the failure happens and once SQL Server is back online, SQL Server reads the transaction log and reruns all the actions that happened until the time of failure. This process of the data manipulations on a database after a failure is called restart recovery. During restart recovery, all the pages referenced in the transaction log will be fetched from disk to the memory and manipulations will be performed; just like how it happened during the original transaction.

The database restart recovery can run longer depending on the number of transactions carried out before the failure. In order to reduce the time taken for the restart recovery, SQL Server does a Checkpoint on each of the databases at frequent intervals. The Checkpoint is a process of writing all pages in memory to the disk, irrespective of whether the transaction is committed or not. When SQL Server will have to perform the restart recovery only for the transactions happened after the latest checkpoint. Hence Checkpoints reduces the time taken during the restart recovery.

System failure Example

Let’s us consider the following scenarios to explain the above concepts.

Consider there are five transactions at different stages of execution while checkpoints happen at frequent intervals. The figure shows the progress of each of the transactions at a time. There are 2 checkpoints C1 and C2 and a system failure SF. The figure also shows that

Transaction 1 (T1) is complete before the checkpoint C1 happened. Hence the data is modified and retained in memory until the checkpoint happens.

Transaction 2 (T2) started before the checkpoint but was in progress at the time of the checkpoint. Upon Checkpoint All the data modified in the memory before the checkpoint will be written to disk, even though the transaction is not committed. There is a discrepancy between the pages in memory and those in a disk. But the transaction is complete before failure, hence the log will have information of whether the transaction got committed or not.

Transaction 3 (T3) started before the checkpoint and was in progress till the system failed. This is more like the transaction 2, but the transaction is not complete at the time of failure. Hence Log will not have information if it is committed or not.

Transaction 4 (T4) started after the checkpoint but completed before the system failure. Here all the data is modified in the memory and logged to disk. Hence no updated information will be available in the data file after the failure.

Transaction 5 (T5) started after the checkpoint and was running during the failure. Hence no updated information will be available in the data file at the time of failure.

TransactionLog-Checkpoint

Fig: Actions taken for different transactions after recovery

The following actions will be taken during the restart recovery for each of the scenarios considered.

  1. Transaction 1: No action will be taken on the      Transaction 1, as the modified data in memory is already written to disk by the checkpoint.
  2. Transaction 2: Since half of the transaction is already written to disk, pages from the disk will be read and put into memory.      Since the Transaction is completed before the failure all the activities are available in the transaction log and each of the data manipulation done after the checkpoint will be re-run. This is called roll forward.
  3. Transaction 3: Half of the transaction is already written to disk, pages will be fetched into memory and all the actions carried out after the checkpoint will be read from the transaction log and re-done. But the transactions will rollback as there is no information if it is committed or not.
  4. Transaction 4: All the data modified in the memory will be lost as the failure happened before the checkpoint. The transaction log will have information regarding what changed and if it’s committed or not.      Hence all the activities will be performed again and the truncation will be completed.
  5. Transaction 5: All the data modified in the memory will be lost as the failure happened before the checkpoint. And the transaction log will not have information if it’s committed or not. Hence it will be rolled back.

Conclusion

The transaction log is a vital component in making databases faster and easy to recover during failure. Understanding the basics of the transaction log is important in making a good DBA or a programmer. In recent times transaction log is used as a basis for data reliability and scalability technologies like log shipping, Mirroring, Replication, Change data capture, change control etc. For further reading regarding the transaction log, below are few pointers

http://msdn.microsoft.com/en-us/library/ms345583.aspx

http://www.sqlservercentral.com/articles/Design+and+Theory/63350/