SQL Server Recovery Models

When I review customers database environments, I always check to see what the recovery model is for their databases and review that with the customer. I often times have to explain the three recovery models and what they are used for. Simple, Full, and Bulk-Logged. The recovery model that you select will determine how transaction logs and backups are managed for the database.

Simple Recovery Model

The Simple recovery model automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage transaction log space. Due to this behavior, point-in-time restores are now an option.

When I find customers with databases in the Simple recovery model, I always makes sure that they understand that transaction log backups are not available for those databases and that they will only be able to recovery up to the point of the last full backup. If they are ok with that, the database can keep the Simple recovery model, otherwise it needs to be changed to support their service level agreement.

Full Recovery Model

The Full recovery model requires regular transaction log backups in order to manage the size of the transaction log. By taking regular transaction log backups, this allows for a point-in-time recovery point. For important databases where data loss would be an issue, the Full recovery model is needed.

Most organizations settle on a standard transaction log backup interval. My experience has shown that most organizations choose a 15-minute backup interval. I’ve seen this as low as every 5 minutes.

Some common issues that can arise from having a database in the Full recovery model is that the transaction log can grow rapidly if not managed properly. For example, if a database is in Full recovery model without having transaction log backups, the log will just continue growing. If someone has a very long running transaction, the portion of the log being used by that transaction cannot be reused which can cause the transaction log to have to grow.

Bulk-Logged Recovery Model

The Bulk-Logged recovery model is adjunct to the full recovery model in that it allows for high-performance bulk copy operations. This reduces log space usage for most bulk operations. Bulk-Logged recovery model requires transaction log backups just like the Full recovery model. This recovery model is designed for databases that see frequent bulk operations like bulk inserts or index rebuilds. While bulk operations are being performed, minimal logging is being performed.

I don’t see Bulk-Logged used much in the field, however it can be a good option for data warehouses..