Recovery models

  • Article

Recovery model is our way to instruct SQL Server how to maintain the log file.

We have 3 recovery models in SQL Server – SIMPLE, BULK_LOGGED and FULL.

We’ll review them and describe the differences between them.

SIMPLE recovery model

When the database recovery model is set to SIMPLE, every DML and DDL statement is written to the transaction log.
It is done in order to support the basic recovery ability; For example, long operation being cancelled due to user request or server shutdown. In this case, SQL Server will rollback the unfinished operation based on log records in the log file and will keep our database consistence. Log records are cleaned / flushed after the transaction has been committed and checkpoint occurred.

BULK_LOGGED recovery model

When the database recovery model is set to BULK_LOGGED, it acts exactly like as the SIMPLE recovery mode, except the log cleaning. Log records are written into the log file and transferred to the data files in same way as in SIMPLE recovery model (Checkpoint, Lazy writer and Eager writer); however these records are not cleaned out of the log file automatically. Log records will stay inside the log file until “BACKUP LOG” command executed. Log backups will provide us additional recovery/restore options to the existing in SIMPLE recovery model.

FULL recovery model

When the database recovery model is set to FULL, all aspects of log cleaning work are functioning like in the BULK_LOGGED recovery model. There are 2 major differences in recovery options:

  1. What is written to the log file - For regular DMLs like insert, update, delete and merge SQL Server logs same records into the log file. The difference is in "minimally logged operations" logging. Under FULL recovery model all operations, including "minimally logged operation", are fully logged. It is done in order to support the additional restore options.
  2. Recovery accuracy - We have more things written into the log file, so we have more “detailed” transaction log backups. It provides us additional restore options (we will discuss it in future posts.

Things we need to remember:

  1. Before changing the recovery model, we need to make sure that we fully understand the recovery needs of our environment, and how it meets our business needs.
  2. Once we change the recovery model to BULK_LOGGED or FULL by the “ALTER DATABASE” statement, or with the assistance of the SQL Server Management Studio, we are required to perform a full or differential database backup, in order to start log sequence. Otherwise the database will continue working in SIMPLE recovery model.
  3. There are additional processes that may affect log cleaning, but they aren’t related to recovery model, thus won’t be discussed in this post. (Transactional Replication for example).
  4. The log backup file created for the database in BULK_LOGGED recovery model might be significantly bigger than the log file itself (in case the log file contains records of minimally logged operations).
  5. Starting SQL Server 2008, we can enjoy "minimally logging" for insert and merge statements under specific circumstances.

Summary:

  1. Chose recovery model wisely according to your recovery and business needs.
  2. Perform full or differential backup immediately after changing the recovery model to FULL or BULK_LOGGED.
  3. If you database is in BULK_LOGGED or FULL recovery model, schedule transaction log backups to provide a recovery window and prevent unrestrained log file growth.
  4. Minimally logged operations are minimally logged only in SIMPLE and BULK_LOGGED recovery models. Under FULL recovery model all minimally logged operation are fully logged.
  5. Database log file size may vary because of multiple reasons; the recovery model is a major one. Monitor your log file size and usage; make sure you understand deviations from baseline.
  6. If the database log file grows way beyond the baseline as a result of a one-time maintenance or malfunction, shrink it wisely.