About recovery modes, I know that if you want point in time recovery, we should use FULL recovery mode. if this is not necessary, you can use SIMPLE recovery mode.

But, I read about Simple recovery mode, and it says that we should use this if point in time recovery is not important and if your data is static.

Here we don't need LOG backups, we only use Full backups and Diffs, and I thought that should be good for performance and disk space to use Simple recovery databases. But As I said, we don't have static data. there's a lot of insert, updates and development updates ( yes I don't understand why they don't need point in time recovery , but, anyway...).

My question is, is still a good Idea to use simple recovery, or by not having static data, the log will still grow, and etc?


I am unsure where you got your information from regarding:

But, I read about Simple recovery mode, and it says that we should use this if point in time recovery is not important and if your data is static.

Microsoft states in their article Choosing the Recovery Model for a Database:

The simple recovery model is generally appropriate for a test or development database. However, for a production database, the best choice is typically the full recovery model, optionally, supplemented by the bulk-logged recovery model. However, the simple recovery model is sometimes appropriate for a small production database, especially if it is mostly or completely read-only, or for a data warehouse.

There is an equally interesting article on Microsoft Docs with the title Recovery Models (SQL Server) which goes on to explain the various models and their pros and cons:

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

To answer some of your questions:

  1. The transaction log can still grow to max size or until the disk is full if a transaction is never COMMIT-ed. This is because a CHECKPOINT will have no effect on the LSN in the Transaction Log. Because a transaction is still open the TLog will not be purged.

  2. If you have no requirements for a Point-in-Time recovery and your management is happy to have a restore from the last differential backup, then you could go with the SIMPLE recovery model.

  3. There can still be a performance impact when using the SIMPLE recovery model when 1. occurs. Transactions are still written to the TLog file even in SIMPLE recovery model, they just get purged automatically on each CHECKPOINT and if the SQL Server has resources to spare.

When to use Simple Recovery Model

  • Point of failure recovery is unnecessary. If the database is lost or damaged, you are willing to lose all the updates between a failure and the previous backup.
  • You are willing to risk losing some data in the log.
  • You do not want to back up and restore the transaction log, preferring to rely exclusively on full and differential backups.

Please also read the articles Backup Under the Simple Recovery Model and Restore Restrictions Under the Simple Recovery Model which will explain in depth what you have to expect then relying on a Simple Recovery Model.

PLEASE BE ADVISED: You will lose all your data from the last FULL and/or DIFFERENTIAL backup onwards if you rely on the SIMPLE recovery model.

| improve this answer | |
  • Fantastic answer. – pimbrouwers Jun 7 '18 at 13:10

Ultimately, your recovery model is dictated by your business needs.

It all boils down to the question "How much data is it okay to lose if the server goes belly-up?".

With FULL RECOVERY, that time is dictated by the time between log backups (and can be reduced if a tail-log backup is available). You manage the log backups and how full they get by taking LOG backups. If you don't take log backups, the log will simply continue to fill until it runs out of disk space. At that time, transactions will fail due to insufficient disk space.

With SIMPLE RECOVERY, that time is increased to the time between the current time and your last differential or full backup. The log is managed by CHECKPOINT operations that occur automatically behind the scenes (Recovery Interval). Unless you are doing transactions whose size exceeds the size of the current log file, it won't grow.

This is also under the assumption that your backups do, in fact, work. Backups are only valid if they can be restored successfully.

I'd recommend putting these decisions in the hands of your superiors after you present to them what the differences are and what that means in terms of RPO/RTO.

| improve this answer | |

From a slightly different angle, for anyone who comes along:

I thought that should be good for performance

It's a fairly common misconception that SIMPLE recovery model logs less, and somehow makes ALL modifications faster.

This is only true in limited use cases, where some actions may be minimally (or BULK) logged.

For a full reference, check out the Microsoft Data Loading Performance Guide.

| improve this answer | |

The recovery model should be based on the RPO (Recovery Point Objective) of the application and less to do with performance. How much data can be lost per the business? Can the lost data be reconstituted from other sources? I have some prod systems that are in simple mode because the RPO is 1 day and we have daily backups. I have other prod systems where the RPO is no loss of data... In those situations we have aggressive tlog backup schedules couple with HA features at the server and storage levels...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.