A few weeks ago I released a SQL Server Quickie about Crash Recovery in SQL Server. There was one interesting comment about whether the Recovery Model of the database influences Crash Recovery and if you would lose transactions in the Recovery Model Simple. Therefore I want to take this point today and talk in more detail about it.

Recovery Models

The short answer to this question is a simple NO: the Recovery Model doesn’t influence Crash Recovery and NO you will NOT lose transactions with the Recovery Model Simple. So what is the purpose of the Database Recovery Model?

With the Recovery Model you only tell SQL Server how to handle the Transaction Log. SQL Server offers the following 3 Recovery Models:

• FULL
• SIMPLE
• BULK_LOGGED

Let’s talk about them in a little bit more detail. By default every new database is in the Recovery Model Full (after a Full Database Backup!). The Recovery Model Full means that you need to perform regular Transaction Log Backups. After the Transaction Log Backup SQL Server can mark VLFs (Virtual Log Files) as Inactive, and can override them at a later point in time. If you don’t perform regular Transaction Log Backups, SQL Server can’t override VLFs (because they are all Active), and as a result your Transaction Log will grow.

Growing the Transaction Log is slow (because of the necessary Zero Initialization) and leads to Log Fragmentation (a huge amount of different VLFs). Another benefit of regular Transaction Log Backups is the fact that you can perform a so-called Point in Time Recovery, where you restore your database to a certain point in time.

If you don’t care about your data (I am being sarcastic!), you can switch the Recovery Model of your database to Simple. With the Recovery Model Simple you don’t need to bother yourself with regular Transaction Log Backups, because SQL Server marks VLFs as Inactive during the CHECKPOINT process. If you have no long-running transactions, your Transaction Log will not grow that much in this Recovery Model. One side-effect of the Recovery Model Simple is that you can’t perform a Point in Time Recovery.

When your database crashes or get corrupted, you can only restore your latest Full Database Backup together with optional Differential Backups. How much data you lose in this scenario depends on the age of your latest Full/Diff Backup. I never ever recommend Recovery Model Simple for OLTP databases. In Data Warehousing scenarios it makes more sense, because the loaded data isn’t changing anymore.

And finally SQL Server offers you the Recovery Model Bulk Logged. When you run certain operations in SQL Server, they can be Minimally Logged (there is nothing like a No-Logged Operation in SQL Server!) if you are in the Recovery Model Bulk Logged. Minimally Logged means that SQL Server doesn’t write individual Transaction Log Records into the Transaction Log, when you run such an operation.

SQL Server only marks the changed Extents as Changed through the so-called BCM Page (Bulk Changed Map Page). As an outcome your Transaction Log will not get that big when you run a Minimally Logged Operation. The negative side-effect is that you can’t perform a Point in Time Recovery during that interval where your database was in the Recovery Model Bulk Logged. Therefore you should always minimize the time in which a database is in this Recovery Model. For example: you only switch from Full into Bulk Logged (and finally back to Full), when you run a Minimally Logged Operation.

When you are in the Recovery Model Bulk Logged, you still have to perform Transaction Log Backups. But their size will not be smaller, because SQL Server copies the changed Extents (based on the BCM page) into the Transaction Log Backup – as you can see from the following picture.

Summary

The Crash Recovery process is in no way influenced by the Recovery Model. The Recovery Model only influences your Transaction Log, and whether you can perform a Point in Time Recovery. As a general recommendation you should always use the (default) Recovery Model Full in combination with regular Transaction Log Backups. With that approach you have the possibility to minimize your data loss accordingly.

Thanks for your time,

-Klaus