SQL Server A to Z – Recovery Models


I may be on vacation, but the alphabet goes on.  We’re up to the letter R and that stands for rest, relaxation, and recovery models!

Recovery models define how SQL Server manages the transaction log for your database.  You have three options to choose from: Simple, Full, and Bulk-logged.  The model you choose will determine how much of your data you can recover in the event of a restore.

What is a checkpoint?

Before we get into the three models and how they work, it’s important to understand what a checkpoint is.  When you modify data in a database, that changed data isn’t written directly to disk, it’s written to the buffer cache.  On a regular basis, SQL Server takes all of those changed buffer pages, called dirty pages, and writes (or hardens) them to disk.  This is a checkpoint.

The first thing the checkpoint does is write a record to the transaction log marking the start of the checkpoint.  It also notes the Minimum Recovery Log Sequence Number (MinLSN).  The MinLSN is the minimum of:

  • the LSN of the start of the checkpoint
  • the LSN of the start of the oldest active transaction
  • if your database is being replicated, the LSN of the start of the oldest transaction that has not been delivered to the distribution database

This LSN marks the point to which the database can rollback in the event of recovery and ensure data consistency.  It also marks the beginning of the active portion of the transaction log.  Any records in front of that MinLSN are considered inactive tranactions.
The checkpoint then writes the dirty buffer pages to disk and writes a record to the transaction log marking the end of the checkpoint.  The LSN of the start of this whole process is then recorded to the database boot page.

So now that we know what a checkpoint is, what does this all have to do with recovery models?  Quite a bit.

Simple

With the Simple recovery model, the transaction log is truncated with each checkpoint.  Right before the checkpoint writes the dirty buffer pages to disk it marks the inactive portion of the log (the part before the MinLSN) as reusable.  No transaction log backups occur, or are even possible.  For this reason, it is only possible to restore your database up to the end of the last full or differential backup.  Any transactions that have taken place since then will be lost.  Simple mode is most commonly found in development databases, where the ability to recover to a point in time isn’t required.

Full

The most commonly used recovery model in Production databases is Full.  In this model, all transactions are fully logged to the transaction log.  Log backups are required here.  The inactive portion of the transaction log is truncated after transaction log backup occurs, as long as a checkpoint has occurred since the last log backup.  Because the transaction log is being backed up, recovering to a point in time is possible using Full mode.

Bulk-logged

The least-used of the recovery models is Bulk-Logged, probably because it’s also the least understood.  Bulk-Logged is similar to Full, in that operations are recorded the the transation log and log backups are still required.  With Bulk-Logged mode, however, bulk operations like large data imports (BULK INSERT, bcp) or index maintenance are minimally logged.  This improves performance of those operations by reducing overhead.  However, in order to maintain data integrity during a recovery, the data pages affected by those minimally logged operations are included in the transaction log backup.  This can results in some very large log backup files, so be aware.  Also, you can’t restore to a point in time within a log backup that contains bulk-logged operations.  You have to restore that entire log.

You’re probably not going to keep your database in Bulk-Logged mode.  It’s generally recommended that you use Full recover model, and switch to Bulk-Logged only when performing bulk operations.  Then switch back to Full when you’re done.   It’s a good idea to take a log backup right before switching to Bulk-Logged mode and right after switching back to Full mode in order to minimize your exposure.
Further Reading

For more information about SQL Server recovery models, check out the following resources:

Backup Under the Simple Recovery Model

Backup Under the Full Recovery Model

Backup Under the Bulk-Logged Recovery Model

Operations That Can Be Bulk-Logged

Also recommended:

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>