April 29, 2017 at 13:15SQL101: Change Recovery Model
- Transfer
- Tutorial
Note from the translator: 101 in American colleges usually designates an introductory course that gives basic knowledge about the subject area.
SQLskills launches a new initiative for posting records with basic knowledge, we called it SQL101. We will write about things that, as we often see, are done incorrectly, technologies that are used incorrectly, and many misunderstandings that lead to serious problems. If you want to find all the entries in this series, check out the link SQLskills.com/help/SQL101 (English).
One of the things that may lurk you in administering is the effect of temporarily switching from a full recovery model to another. In this article, I will briefly describe three recovery models and the problems that can arise when switching from a full model to a simple one and from a full model to a model with bulk-logged.
There are three recovery models:
Most people use the full recovery model to be able to back up transaction logs and afford all possible recovery options. The main thing you should remember when your database uses a full recovery model or a model with incomplete logging - you must periodically back up the transaction log or the transaction log will grow indefinitely.
In some circumstances, a simple recovery model is preferable: if you do not need the ability to restore at a point in time and minimize losses during recovery using transaction log backups. An example is the database used for tests, which is replenished once a day and any changes can be lost or quickly repeated.
Often I hear from people that they switch to a simple recovery model to avoid the growth of the transaction log when performing batch downloads or rebuilding indexes, whereas what they really need is a model with incomplete logging. There are also persistent myths that some regular operations * require * that the recovery model be simple - it’s just (ha ha) not true.
Switching to a simple recovery model breaks the chain of transaction log backups, requiring a full or differential backup to be created before further creating transaction log backups.
Moreover, such a switch limits your capabilities in the event of a failure, because now you have only one full backup left from which you can recover - the one you made last. Think about it - your possible recovery options become like this:
If this latest full backup (before or after switching to a simple recovery model) is corrupted, you cannot recover. You cannot take the previous full backup, because it will allow you to recover only until you switch to a simple recovery model, but not after. Well, I think you can do it, but you will lose all the work from the moment you switch to a simple recovery model.
Switching to a simple recovery model is not something you do automatically or regularly. The only time you can do this temporarily is when your transaction log is full and you don’t have a single opportunity to clear it (for example, you cannot backup it or add another log file), except to switch to a simple recovery model and forcibly invoke the checkpoint creation operation. In this case, you are taking radical measures to allow you to work with the database, and are fully aware of the limited recovery opportunities that you now have.
Until you get into this emergency, you either decide to use a simple recovery model on an ongoing basis, or you should never switch to it.
Switching to incomplete logging during the loading or maintenance of indexes is acceptable to avoid transaction log growth. In fact, switching back and forth between the full recovery model and the incompletely logged model does not in any way affect the chain of backups of the transaction log. And such a switch does not affect log shipping or replication, but you cannot switch from a full recovery model when using database mirroring or AlwaysOn availability groups because they require a full recovery model.
However, using a recovery model with incomplete logging can cause problems for recovery from failures, so even if you want to use such a recovery model because of its features, you can refuse to use it to avoid the risks of losing some of the recovery capabilities.
Problem 1: A transaction log backup that contains a minimally-logged operation cannot be used in point-in-time recovery. This means that the time you specify in the WITH STOPAT clause in the restore command cannot be the time that belongs to such a backup. You can use such a backup as part of the recovery chain and stop at any time after it (unless this time refers to another backup that also contains minimally logged operations, of course), but not during the time that belongs to this backup.
Problem 2: If you need to back up the final fragment to capture all the transaction log entries created since the last scheduled backup of the transaction log, while the data files are inaccessible or damaged, and the transaction log entries that need to be backed up contain a minimum logged operations, when creating such a backup you will receive an error in versions prior to SQL Server 2008 R2, and starting with the version of SQL Server 2008 R2 you will receive a successfully created backup, cat If damaged, the database will be damaged.
Therefore, if you intend to use a model with incomplete logging to save a place in the transaction log at the time of large operations, you need to make sure that a) there is no chance that you will want to recover at the point in time between the last and the next transaction log backup ; and b) there will be no changes in the database that you cannot repeat in the event of a failure and you cannot make a correct backup copy of the final fragment.
Switching a recovery model between a full model and an incompletely logged model may not be as secure as you think.
For each database that you are responsible for, make sure that you understand the implications of changing the recovery model, since such a change can lead to recovery problems during failures.
SQLskills launches a new initiative for posting records with basic knowledge, we called it SQL101. We will write about things that, as we often see, are done incorrectly, technologies that are used incorrectly, and many misunderstandings that lead to serious problems. If you want to find all the entries in this series, check out the link SQLskills.com/help/SQL101 (English).
One of the things that may lurk you in administering is the effect of temporarily switching from a full recovery model to another. In this article, I will briefly describe three recovery models and the problems that can arise when switching from a full model to a simple one and from a full model to a model with bulk-logged.
Recovery Models
There are three recovery models:
- Full recovery model (used by default and most often)
- All changes in the database are fully logged. This does not mean that each change has a separate log entry, since some operations are written with fewer log entries, but nevertheless the full effect of the operation is logged (for example, the TRUNCATE TABLE operation - see the full explanation here (English)).
- The transaction log will not be cleared (i.e., parts of it will not be available for reuse) until the backup of the transaction log is made (see the full explanation here (English)).
- All recovery options are available when the database is in the full recovery model (and has been in it since the last backup was created).
- All changes in the database are fully logged. This does not mean that each change has a separate log entry, since some operations are written with fewer log entries, but nevertheless the full effect of the operation is logged (for example, the TRUNCATE TABLE operation - see the full explanation here (English)).
- Incomplete Logging Recovery Model
- Some changes (such as rebuilding the index or batch loading, but NOT the standard INSERT / UPDATE / DELETE) can be minimally logged, which reduces the number of log entries and the transaction log does not become too large during these operations. Note that this does not change the size of subsequent transaction log backups. For complete instructions on how to make your operations minimally logged, see the Data Loading Performance Guide (English), which describes all the possible conditions that must be met.
- The transaction log will not be cleared (i.e., parts of it will not be available for reuse) until the backup of the transaction log is made (exactly the same as with the full recovery model).
- Using a recovery model with incomplete logging, you lose some of the recovery options (point-in-time recovery and a backup of the final fragment) in exchange for a performance improvement associated with minimally logged operations.
- Some changes (such as rebuilding the index or batch loading, but NOT the standard INSERT / UPDATE / DELETE) can be minimally logged, which reduces the number of log entries and the transaction log does not become too large during these operations. Note that this does not change the size of subsequent transaction log backups. For complete instructions on how to make your operations minimally logged, see the Data Loading Performance Guide (English), which describes all the possible conditions that must be met.
- Simple recovery model
- Some changes can be minimally logged (exactly the same as with the recovery model with incomplete logging).
- The transaction log will not be cleared until the checkpoint creation operation (CHECKPOINT) is performed - it usually runs automatically.
- Transaction log backups are not possible, so you have the most limited number of recovery options.
- Some changes can be minimally logged (exactly the same as with the recovery model with incomplete logging).
Most people use the full recovery model to be able to back up transaction logs and afford all possible recovery options. The main thing you should remember when your database uses a full recovery model or a model with incomplete logging - you must periodically back up the transaction log or the transaction log will grow indefinitely.
In some circumstances, a simple recovery model is preferable: if you do not need the ability to restore at a point in time and minimize losses during recovery using transaction log backups. An example is the database used for tests, which is replenished once a day and any changes can be lost or quickly repeated.
Switch to a simple recovery model
Often I hear from people that they switch to a simple recovery model to avoid the growth of the transaction log when performing batch downloads or rebuilding indexes, whereas what they really need is a model with incomplete logging. There are also persistent myths that some regular operations * require * that the recovery model be simple - it’s just (ha ha) not true.
Switching to a simple recovery model breaks the chain of transaction log backups, requiring a full or differential backup to be created before further creating transaction log backups.
Moreover, such a switch limits your capabilities in the event of a failure, because now you have only one full backup left from which you can recover - the one you made last. Think about it - your possible recovery options become like this:
- A full backup after switching to a simple recovery model, differential backups after this full (if you use differential backups) and any transaction log backups since you switched back from a simple model; or
- The most recent full backup before switching to a simple recovery model plus the last differential backup after switching back from a simple plus any transaction log backups.
If this latest full backup (before or after switching to a simple recovery model) is corrupted, you cannot recover. You cannot take the previous full backup, because it will allow you to recover only until you switch to a simple recovery model, but not after. Well, I think you can do it, but you will lose all the work from the moment you switch to a simple recovery model.
Switching to a simple recovery model is not something you do automatically or regularly. The only time you can do this temporarily is when your transaction log is full and you don’t have a single opportunity to clear it (for example, you cannot backup it or add another log file), except to switch to a simple recovery model and forcibly invoke the checkpoint creation operation. In this case, you are taking radical measures to allow you to work with the database, and are fully aware of the limited recovery opportunities that you now have.
Until you get into this emergency, you either decide to use a simple recovery model on an ongoing basis, or you should never switch to it.
Switching to a partially logged recovery model
Switching to incomplete logging during the loading or maintenance of indexes is acceptable to avoid transaction log growth. In fact, switching back and forth between the full recovery model and the incompletely logged model does not in any way affect the chain of backups of the transaction log. And such a switch does not affect log shipping or replication, but you cannot switch from a full recovery model when using database mirroring or AlwaysOn availability groups because they require a full recovery model.
However, using a recovery model with incomplete logging can cause problems for recovery from failures, so even if you want to use such a recovery model because of its features, you can refuse to use it to avoid the risks of losing some of the recovery capabilities.
Problem 1: A transaction log backup that contains a minimally-logged operation cannot be used in point-in-time recovery. This means that the time you specify in the WITH STOPAT clause in the restore command cannot be the time that belongs to such a backup. You can use such a backup as part of the recovery chain and stop at any time after it (unless this time refers to another backup that also contains minimally logged operations, of course), but not during the time that belongs to this backup.
Problem 2: If you need to back up the final fragment to capture all the transaction log entries created since the last scheduled backup of the transaction log, while the data files are inaccessible or damaged, and the transaction log entries that need to be backed up contain a minimum logged operations, when creating such a backup you will receive an error in versions prior to SQL Server 2008 R2, and starting with the version of SQL Server 2008 R2 you will receive a successfully created backup, cat If damaged, the database will be damaged.
Therefore, if you intend to use a model with incomplete logging to save a place in the transaction log at the time of large operations, you need to make sure that a) there is no chance that you will want to recover at the point in time between the last and the next transaction log backup ; and b) there will be no changes in the database that you cannot repeat in the event of a failure and you cannot make a correct backup copy of the final fragment.
Switching a recovery model between a full model and an incompletely logged model may not be as secure as you think.
Summary
For each database that you are responsible for, make sure that you understand the implications of changing the recovery model, since such a change can lead to recovery problems during failures.