SQL SERVER – Simple Recovery Model and Restrictions

Simple Recovery changes recovery options before it changes a performance diagnosis. I choose it from the agreed recovery requirements.

During a performance health check, a client asked whether switching from full to simple would make the server faster. I have not seen that change produce a miraculous improvement. It changes recovery options before it changes a tuning diagnosis.

What simple recovery does not provide

• Transaction log backups and restores to an arbitrary point between data backups.
• Log shipping and database mirroring for that database.
• Participation as a database in an Always On availability group, which requires full recovery.

Always On covers different technologies. A failover cluster instance differs from an availability group. These restrictions don’t prohibit every feature described as Always On.

This model still records transactions in the log. Checkpoints make inactive log space eligible for reuse when other conditions allow it. An open transaction or another reuse hold can still prevent truncation.

Check the current choice

Read the Simple Recovery setting before planning a change

SELECT name, recovery_model_desc
FROM sys.databases
WHERE database_id = DB_ID();

This reads the recovery model of the current database. It does not change it. Agree on the recovery objective and existing dependencies before considering a switch. Returning to full recovery also requires establishing the appropriate backup chain.

If your workload is slow, investigate the workload. Don’t discard recovery capabilities because someone described this model as a faster mode.

Start with the restore that the business needs

Imagine that the latest usable data backup represents the database at 10:00, and an unwanted change occurs at 10:15. If the restore plan requires returning to a moment just before that change, recovering only the earlier data backup does not meet the requirement. Agree on the acceptable data-loss interval before deciding which model and backup schedule to use.

If the current choice already meets the requirement, keep the performance investigation focused on evidence such as the slow query, blocking, execution plan and resource use. A different restore capability is not a substitute for diagnosing the workload. Likewise, a growing log needs a review of the reason space cannot be reused, rather than an assumption that the setting alone explains it.

When returning from this model to full recovery, take an appropriate full or differential database backup to establish the log chain, then schedule regular transaction log backups. Update and rehearse the restore sequence as part of the change. The setting, the backup jobs and a tested recovery plan must work together.

Related reading

• Comprehensive Database Performance Health Check
• CONCAT and NULL – SQL in Sixty Seconds #123
• Sleeping vs Suspended Process – SQL in Sixty Seconds #122
• Pinned Tab – SSMS Efficiency Tip – SQL in Sixty Seconds #121
• Split Screen for SSMS Efficiency – SQL in Sixty Seconds #120
• Nonclustered Primary Key – SQL in Sixty Seconds #119
• Recent Execution of Stored Procedure – SQL in Sixty Seconds #118
• Full-Screen Execution Plan – SQL in Sixty Seconds #117

The recovery model is not a faster-mode switch, it is a choice about backup and recovery capabilities.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.