In the previous articles of this series (see TOC at bottom), we discussed the main concept of the SQL Server transaction, the internal architecture of the SQL Server Transaction Log and finally the Virtual Log File and the best practices in managing the VLFs. In this article, we will go through the relationship between the SQL Server Transaction Log and the three database recovery models.
Recovery Models Overview
A recovery model is a database property that defines how the SQL Server Engine treats the database transaction logs including
- where it specifies how these transactions will be logged and saved to the SQL Server transaction log file
- the types of backup and restore operations that can be performed on the database
- the high availability or disaster recovery solutions that are supported on the database
- and the recovery point in time that we can recover the database to
There are three recovery model types in SQL Server: Full, Bulk-logged and Simple recovery models. To get an in-depth understand for these recovery models, we will discuss each recovery model individually with its relationship with the SQL Server transaction log.
The recovery model of a newly created database will be the same as the recovery model of the model system database. You can change the recovery model of an existing database from the Options page of the database properties window, using the SQL Server Management Studio.
To do that, right-click on the database and choose the Properties options. From the Database Properties window, move to the Options page and choose the new recovery model of your database from the Recovery Model drop-down list, as shown below:
You can also change the recovery model of the database using the ALTER DATABASE T-SQL command below:
|
1 2 |
ALTER DATABASE [ApexSQL_Log_Test] SET RECOVERY SIMPLE GO |
Simple Recovery Model
When the database is configured with Simple recovery model, the SQL Server Engine stores the SQL transaction logs in the transaction log file for a short time while the transaction is active. After writing the SQL Server transaction log records from the buffer cache to the SQL transaction log file in the disk drive, the log is truncated when a checkpoint operation is performed to commit the transactions. For more information about the checkpoints, check the Database Checkpoints.
A database with Simple recovery model supports only two types of backup, the Full backup and Differential backup, with no possibility for the SQL Server Transaction Log backup here. You may count that as an advantage for the Simple recovery model, as the process of administrating and maintaining the databases with Simple recovery model is piece of cake, and it handles the SQL transaction log file truncation and reuse automatically.
This scenario is not always optimal because not being able to take a SQL Server Transaction Log backup for the database means that, the data loss possibility in the database with Simple recovery model is larger, as it is not possible to restore a corrupted database with Simple recovery model to a specific point in time. Instead, you will be able only to restore it to the time when the Full backup or the Differential backup is taken.
For example, suppose that the last Full backup is taken today 12 AM, the last Differential backup is taken 12PM and the database corruption occurred at 6 PM. All the database changes performed in the last 6 hours will be lost. This huge amount of data loss cannot be acceptable for the heavily transactional production systems. In some cases, this can be acceptable in case of read-only or development databases with minimal possible changes on the data, unless your increase the frequency of the Full or Differential backup operations, which is not possible in most cases, due to backup performance overhead purposes.
On the other hand, a database with Simple recovery model cannot participate in most high availability and disaster recovery solutions, such as the Log Shipping, Database Mirroring and the Always On Availability Groups, as the SQL transaction logs are not kept in the SQL Server transaction log file after committing the transaction.
Full Recovery Model
If the database is configured with Full recovery model, the SQL Server Engine keeps the transaction logs in the SQL transaction log file as inactive, after committing the transaction and performing a checkpoint operation, and will not be truncated until a SQL transaction log backup is performed.
From the administrative side, extra effort is required to monitor and manage the SQL transaction log file size and prevent it from running out of free space or filling the disk drive. This can be done by automating a SQL transaction log backup maintenance plan to truncate the inactive portion of the SQL transaction log, and include it to the complete backup strategy with the Full and the Differential backup jobs. For more information about SQL transaction log administration, check Managing SQL Server Transaction Logs.
If you keep an open mind, you will see that behind this extra effort there are great benefits. Keeping the transaction log records in the SQL transaction log file, allows us to design a high availability and disaster recovery solutions on that database, such as Database Mirroring, Log Shipping or Always on Availability Groups.
In addition, the backup files generated from the complete backup strategy plan, that consists of the Full, Differential and SQL Transaction Log backup job, and scheduled based on the company disaster recovery plan, will be easily used to restore the database to a specific point in time, in case of database failure or corruption. And with the proper schedule of the three backup types combination, the possibility of any data loss will be minimized or completely prevented, making it the best choice for the production environments.
In the previously discussed example, the last Full backup is taken 12 AM, the last Differential backup taken 12PM and the last hourly SQL transaction log backup is taken at 6PM. If the database corruption occurred at 6:05 PM, and by restoring the last Full backup, the last Differential backup and the hourly SQL transaction log backup from 12 PM till 6 PM, the data loss will be only the changes performed in the last 5 minutes, rather than the 6 hours data loss, which is acceptable in the production environments, as shown below:
Bulk-logged Recovery Model
The third recovery model in SQL Server, that is not highly used, is the Bulk-logged recovery model. It is a special purpose recovery model, in which the SQL Server Engine treats the transaction logs similar to what is described in the Full recovery model, except the way it follows to log the bulk data insertion and modification operations.
When configuring a database with the Bulk-logged recovery model, the SQL Server Engine will use the minimal logging techniques to log the bulk data modification and insertion operations. Minimally logging means logging only the information that is required to recover the transaction without supporting point-in-time recovery, which provides the best possible performance and the least space consumed during these large scale bulk operations. These bulk operations include the BULK INSERT, SELECT INTO and index rebuild operations.
Keeping the database in Bulk-logged recovery model for long time is not recommended. It can be a very short transition period from the Full recovery model to the Bulk-logged recovery model when planning to do bulk operations, then switch back to Full recovery model when the bulk operation complete. To prevent any data loss, it is recommended to take a SQL transaction log backup directly before switching from Full to Bulk-logged recovery model and take another SQL transaction log backup after switching from the Bulk-logged to the Full recovery model. In this way, you will minimize the data loss by ensuring that the least possible number of transaction logs are included within the log that contains the minimally logged bulk operations.
The image below shows the steps that should be performed to gain the best performance during the bulk operations and minimize the possibility of the data loss in case of any database corruption issues:
After understanding the relationship between the SQL Server Transaction Log and the recovery models, you are ready to move to the next article, in which we will describe the relationship between the SQL Server Transaction Log and the different types High Availability and Disaster Recovery Solutions. Stay tuned!
Table of contents
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021