SQL101: Change Recovery Model
Note from the translator: The number 101 in American colleges usually denotes an introductory course, which gives a basic knowledge of the subject area.
SQLskills launches new record placement initiative 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 records in this series, check out the link SQLskills.com/help/SQL101 (English).
One of the things that may lie in wait for you during administration is the effect of a temporary switch from the full recovery model to another. In this article, I will briefly describe the three recovery models and problems that can arise when switching from a full model to a simple one and from a full to a model with incomplete logging (bulk-logged).
')
There are three recovery models:
Most people use the full recovery model to be able to back up the transaction log and afford all the possible recovery options. The main thing you should remember when your database uses the full recovery model or the model with incomplete logging - you must periodically back up the transaction log or the transaction log will grow indefinitely.
In some conditions, a simple recovery model is preferable: if you do not need the ability to recover at a point in time and minimal losses when recovering using transaction log backups. An example would be the test database used, which is refilled once a day and any changes may be lost or quickly repeated.
I often hear from people that they are switching to a simple recovery model to avoid the growth of the transaction log when performing batch loading 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 is simple - it's just (ha ha) not true.
Switching to a simple recovery model breaks the chain of transaction log backups, requiring the creation of a full or differential backup before further backing up the transaction log.
Moreover, such a switch limits your possibilities in case of failure, because now you have only one full backup, from which you can recover - the one you did last. Think about it - your possible recovery options are as follows:
If this latest full backup (before or after switching to a simple recovery model) is damaged, you cannot recover. You cannot take the previous full backup, because it allows you to recover only before switching to the simple recovery model, but not after. Well, I think you can do it, but you 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 temporarily do this is when your transaction log is full and you have no opportunity to clear it (for example, you cannot back it up or add another log file), except to switch to a simple recovery model and force the operation to create a control point. In this case, you take drastic measures to allow you to work with the base, and are fully aware of the limited recovery opportunities you now have.
Until you fall into this emergency, you either decide to use the 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 the growth of the transaction log. In fact, switching back and forth between the full recovery model and the incomplete logging model does not in any way affect the chain of backups of the transaction log. And such switching does not affect log shipping or replication, but you cannot switch from the full recovery model when using database mirroring or AlwaysOn availability groups, since they require the full recovery model.
However, using the recovery model with incomplete logging can cause problems for recovery in case of failures, so even if you want to use this recovery model due to its features, you can refuse to use it to avoid the risk of losing some of the recovery opportunities.
Issue 1: A transaction log backup that contains a minimally logged operation cannot be used in recovery at a point in time. This means that the time you specify in the WITH STOPAT clause in the recovery 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 for any time after it (unless this time refers to another backup that also contains minimally logged operations, of course), but not during the time belonging to this backup.
Issue 2: If you need to perform a backup of the final snippet to capture all transaction log entries created since the last scheduled backup of the transaction log, the data files are unavailable or damaged, and the transaction log entries that need to be backed up have a minimum journaling operations, then when creating such a backup, you will receive an error in versions prior to SQL Server 2008 R2, and starting with SQL Server 2008 R2, you will receive a successfully created backup, oraya will damage the database if it is restored.
Therefore, if you intend to use the model with incomplete logging to save space in the transaction log at the time of large transactions, you need to make sure that a) there is no likelihood that you will want to recover from the time between the last and next backup of the transaction log ; and b) there will be no changes in the database, which you cannot repeat in case a failure occurs and you cannot make a correct backup of the final fragment.
Switching a recovery model between a full model and an incomplete-log model may not be as safe as you think.
For each database 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 in case of failures.
SQLskills launches new record placement initiative 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 records in this series, check out the link SQLskills.com/help/SQL101 (English).
One of the things that may lie in wait for you during administration is the effect of a temporary switch from the full recovery model to another. In this article, I will briefly describe the three recovery models and problems that can arise when switching from a full model to a simple one and from a full to a model with incomplete logging (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 a smaller number of log entries, but the full effect of the operation is nevertheless logged (for example, the TRUNCATE TABLE operation — see here the full explanation (English)).
- The transaction log will not be cleared (that is, parts of it will not be available for reuse) until the transaction log has been backed up (see here for a full explanation (English)).
- All restore 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 a smaller number of log entries, but the full effect of the operation is nevertheless logged (for example, the TRUNCATE TABLE operation — see here the full explanation (English)).
- The incomplete logging recovery model
- Some changes (such as rebuilding the index or batch loading, but NOT standard INSERT / UPDATE / DELETE) can be minimally logged, which reduces the number of entries in the log and the transaction log does not become too large during the execution of 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) document that describes all the possible conditions that must be met.
- The transaction log will not be cleared (that is, parts of it will not be available for reuse) until the transaction log has been backed up (absolutely the same as with the full recovery model).
- Using the incompletely logged recovery model, you lose some of the recovery options (point-in-time recovery and backup of the final fragment) in exchange for performance gains associated with minimally logged operations.
- Some changes (such as rebuilding the index or batch loading, but NOT standard INSERT / UPDATE / DELETE) can be minimally logged, which reduces the number of entries in the log and the transaction log does not become too large during the execution of 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) document that describes all the possible conditions that must be met.
- Simple recovery model
- Some changes can be minimally logged (absolutely 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 is usually executed automatically.
- Creating a backup of the transaction log is not possible, so you have the most limited number of recovery options.
- Some changes can be minimally logged (absolutely the same as with the recovery model with incomplete logging).
Most people use the full recovery model to be able to back up the transaction log and afford all the possible recovery options. The main thing you should remember when your database uses the full recovery model or the model with incomplete logging - you must periodically back up the transaction log or the transaction log will grow indefinitely.
In some conditions, a simple recovery model is preferable: if you do not need the ability to recover at a point in time and minimal losses when recovering using transaction log backups. An example would be the test database used, which is refilled once a day and any changes may be lost or quickly repeated.
Switch to a simple recovery model
I often hear from people that they are switching to a simple recovery model to avoid the growth of the transaction log when performing batch loading 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 is simple - it's just (ha ha) not true.
Switching to a simple recovery model breaks the chain of transaction log backups, requiring the creation of a full or differential backup before further backing up the transaction log.
Moreover, such a switch limits your possibilities in case of failure, because now you have only one full backup, from which you can recover - the one you did last. Think about it - your possible recovery options are as follows:
- 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 the simple model; or
- The latest full backup before switching to a simple recovery model plus the last differential 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 damaged, you cannot recover. You cannot take the previous full backup, because it allows you to recover only before switching to the simple recovery model, but not after. Well, I think you can do it, but you 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 temporarily do this is when your transaction log is full and you have no opportunity to clear it (for example, you cannot back it up or add another log file), except to switch to a simple recovery model and force the operation to create a control point. In this case, you take drastic measures to allow you to work with the base, and are fully aware of the limited recovery opportunities you now have.
Until you fall into this emergency, you either decide to use the 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 the growth of the transaction log. In fact, switching back and forth between the full recovery model and the incomplete logging model does not in any way affect the chain of backups of the transaction log. And such switching does not affect log shipping or replication, but you cannot switch from the full recovery model when using database mirroring or AlwaysOn availability groups, since they require the full recovery model.
However, using the recovery model with incomplete logging can cause problems for recovery in case of failures, so even if you want to use this recovery model due to its features, you can refuse to use it to avoid the risk of losing some of the recovery opportunities.
Issue 1: A transaction log backup that contains a minimally logged operation cannot be used in recovery at a point in time. This means that the time you specify in the WITH STOPAT clause in the recovery 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 for any time after it (unless this time refers to another backup that also contains minimally logged operations, of course), but not during the time belonging to this backup.
Issue 2: If you need to perform a backup of the final snippet to capture all transaction log entries created since the last scheduled backup of the transaction log, the data files are unavailable or damaged, and the transaction log entries that need to be backed up have a minimum journaling operations, then when creating such a backup, you will receive an error in versions prior to SQL Server 2008 R2, and starting with SQL Server 2008 R2, you will receive a successfully created backup, oraya will damage the database if it is restored.
Therefore, if you intend to use the model with incomplete logging to save space in the transaction log at the time of large transactions, you need to make sure that a) there is no likelihood that you will want to recover from the time between the last and next backup of the transaction log ; and b) there will be no changes in the database, which you cannot repeat in case a failure occurs and you cannot make a correct backup of the final fragment.
Switching a recovery model between a full model and an incomplete-log model may not be as safe as you think.
Summary
For each database 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 in case of failures.
Source: https://habr.com/ru/post/327606/
All Articles