CODEWITHSUNDEEP
sqlsql-servert-sql
Sean C
Sean C

Reputation: 83

Is it harmful to set recovery to SIMPLE and then back to FULL?

I have a massive transaction that touches upwards of 2 million records and produces upwards of 4 million. For purposes of rolling back on failure, I am hesitant to split this into multiple transactions. However, on testing this transaction, I encountered a 'log_backup' error.

After doing some research, this appeared to be related to using a 'FULL' recovery model, and that I can set this to 'SIMPLE' with a simple command. I placed the following message at the beginning of my transaction:

ALTER DATABASE <Name> SET RECOVERY SIMPLE;

At the end of the transaction, I reversed this with:

ALTER DATABASE <Name> SET RECOVERY FULL;

A couple of questions:

Upvotes: 0

Views: 3755

Answers (2)

Ben Thul
Ben Thul

Reputation: 32667

One of the two measures for recovery is the Recovery Point Objective (RPO)†. In essence, it is an obligation to the owners of the data saying "if we had to restore from a backup, how much data could we lose?". Full recovery model allows for very little data loss in the case of an unplanned recovery by using log backups to get you as close as you can to the event that caused the recovery to be necessary.

By putting the database into simple recovery, you're removing that safety net. If something happened in the middle of your operation, your only recourse would be to recover from backups prior to when you changed the recovery model.

My advice would be to break up your operation into smaller batches and make sure that you can do a logical rollback. What I mean by that is if, for example, you're updating data, keep track of the primary key and pre-update values in a separate table. That way you could, if necessary, use that separate table to put the data back to its original state. Similarly for inserts (keep track of primary key values so you can delete) and deletes (move the whole row).

† The other being Recovery Time Objective (RTO) which is a measure of how long it would take you to actually do the recovery.

Upvotes: 3

Bacon Bits
Bacon Bits

Reputation: 32145

It's not harmful to switch between Full and Simple, per se, but it is destructive.

Switching from Full to Simple will essentially invalidate and purge the transaction log, so any transactions made since the last transaction log backup would no longer be recoverable. If you must do it, then I would make a transaction log backup and database backup prior to the change to Simple as well as a database backup immediately after changing back to Full.

This situation is precisely what the Bulk Logged recovery mode is for. You're allowed to switch to Bulk Logged mode, run statements which satisfy the bulk logged requirements and take advantage of minimal logging, and then switch back to Full recovery without breaking your transaction log backup chain (though you do lose some point in time recovery for minimally logged operations, of course). If you can meet the requirements of bulk logging this is probably the best solution, but it's often not possible.

If you can't do that and still want to use Simple recovery, then there are still other considerations to make.

If you are running a single statement which touches 2 million records and creates 4 million records, then you essentially gain no benefit from switching to Simple recovery at all as far as the disk space you will consume. Running the statement in Simple recovery mode will consume an equal amount of transaction log space for the duration of the transaction. Transactions under Simple recovery are still completely logged because the entire transaction might roll back. It's just that once the transaction is committed and a checkpoint occurs, the log pages are marked as empty. If you don't care about the disk space being used temporarily and just don't want the transactions to show up in your transaction log backups, then this isn't a major issue.

If you're running a stored procedure or a set of statements and not wrapping the entire thing in a single transaction, then you can potentially benefit from Simple recovery mode, but you may want to periodically issue a CHECKPOINT statement to control how much log is being used. By default in Simple recovery mode, the system is supposed to issue an automatic checkpoint when the log is 70% full, but in my experience this doesn't always happen immediately when the server is under heavy load. If you just run everything as quickly as possible, the server may not stop to take the time to clear the log for reuse, so it will just use the whole Simple file, expand it, and continue to use it. Exactly the same problem as Full recovery.

Upvotes: 1

Related Questions