SQL Server offers three recovery models for databases - full, bulk-logged and simple. The differences between them often appear not to be clearly understood and there are frequent questions as to what recovery model should be used under various circumstances.
I'm going to look at the differences between the three and then discuss and debunk some common myths around recovery models.
Logging levels
Before I go onto the recovery models, I need to clarify what the distinction between fully- and minimally-logged is
Fully logged
An operation is fully-logged if it places enough information into the transaction log that it can be redone (say during the restore of a transaction log) solely with the logged information. It does not automatically mean that every row is individually logged into the transaction log. Truncate table, for example, only logs the page de-allocations (as does drop table), but since that is enough information to fully redo the truncate table, that is classified as ‘fully logged’
Minimally logged
An operation is said to be minimally-logged if all it logs are the allocation changes. That means that there is not sufficient information in the log to replay the transaction.
Let’s imagine a bulk insert in bulk-logged recovery model. All it logs are the allocations, not the inserted rows, not the data on the pages. There is no way that transaction can be redone just from the information logged. SQL would be able to reallocate the pages, but it would have no idea what to put on them.
Recovery Models
Right, onto the three recovery models and how they handle the logging and log truncation.
Full Recovery Model
New databases are built off of the model database, so whatever the recovery model is of the model database is what the new database will have as its recovery model when the database is created. The model database is shipped from Microsoft in the Full recovery model, so unless it has been changed then this is what the recovery model that all new databases will be. When a database is in the full recovery model, all operations in that database are fully logged. No exceptions.
In full recovery model, the transaction log is truncated (space in the log marked as reusable) when a transaction log backup occurs. Other backups (e.g. full backup) do not truncate the log. Hence if a database is in full recovery model, transaction log backups MUST be run on a regular basis otherwise the transaction log will grow until it fills the drive or reaches its size limit. (Note that truncating a transaction log does NOT change the size of the transaction log.)
Because of this behaviour, when a database is in full recovery model and the database is damaged in such a way that the transaction log is intact and undamaged, a tail-log backup can be taken which holds the last transactions that occurred in that database. Hence, assuming the database's transaction log is intact, it is possible to restore without data loss in case of a disaster. In addition, because every transaction is fully logged and log records are not discarded until they are backed up it is possible to restore a database that is in full recovery model to any point in time.
Bulk-logged Recovery Model.
In bulk-logged recovery, some operations can be minimally logged. The list of operations that can be minimally logged is not a large one. It is not all the operations in the database by any means. The list is given in books online (http://msdn.microsoft.com/en-us/library/ms191244.aspx ), I'm not going to reproduce them here. Operations other than those mentioned in that list are fully-logged.
In terms of transaction log truncation, bulk-logged recovery behaves like full recovery; the transaction log is only truncated after a transaction log backup. Hence, like with full recovery, transaction log backups must be run on a regular basis, otherwise the log file will grow until it reaches maximum size or fills the disk.
If a disaster occurs to a database in bulk-logged recovery, even if the log file is intact and undamaged, it may not be possible to take a tail-log backup. To take a tail-log backup of a database in bulk-logged recovery where the data file is missing or damaged, there must have been no bulk-operations since the last log backup. Similarly, to restore a database in bulk-logged recovery to a point in time, that time must be within a log interval (time covered by a single log backup) in which no minimally logged operations occurred. If any minimally logged operations occurred within a log interval, the database can be restored only to the beginning or end of that log interval, not to a point in the middle.
Because of these limitations, having a database in bulk-logged recovery increase the chances of data loss in the case of a disaster. Hence it is more common to switch databases temporarily to bulk-logged recovery for certain operations (like index rebuilds) and then back to full recovery afterwards, than to have them in bulk-logged recovery permanently.
There are two other important considerations for bulk-logged recovery:
While bulk-operations log less and hence don’t use as much space in the transaction log as they would if fully-logged, the same is not true for the transaction log backup. Because there’s not enough information in the log to recreate the minimally logged operations, the log backup also includes the data pages themselves. This means that the log backups will not be smaller than they would in full recovery, and in fact may even be larger.
SQL requires that the data pages modified during a bulk operation be written to the data file before the transaction is considered complete as opposed to normally when only the log records must be written before the transaction commits and the data pages are written later. On slower IO subsystems, this may result in the bulk operation being slower than if it were fully-logged.
Simple Recovery Model
In simple recovery, like in bulk-logged recovery, certain operations can be minimally logged. The list of operations that can be minimally logged is the same as the list for bulk-logged recovery. There are no additional operations that are only minimally logged in simple recovery model.
The main difference with simple recovery is that the transaction log is truncated (space marked as reusable) whenever a checkpoint completes. Log backups are not required to mark the log as reusable and, in fact, log backups are not possible in a database using simple recovery.
Because no log backups can be taken of a database in simple recovery, if a database in simple recovery model is damaged, the best that can be done is to restore to the last full or differential backup. Tail log backups cannot be taken (they are log backups and log backups cannot be taken in simple recovery) and point-in-time restores cannot be done. As a result of this, having a database in simple recovery model can be a substantial risk in terms of potential data loss in the case of a disaster. Before placing any database into simple recovery it should be confirmed that, in the case of some disaster that damages the database, losing all work back to the last full or differential backup is acceptable.
Myths and misconceptions
These are common myths and misconceptions that seem to be spread among SQL Server professioanls.
Myth 1: In bulk-logged recovery model updates and deletes are minimally logged
Books Online states all the operations that can be minimally logged and, with the exception of the partial writes to LOB columns, no update operations are minimally logged. Similarly, no delete operation is ever minimally logged.
Myth 2: In simple recovery there are some operations that don't log anything
There is no such thing as an unlogged operation in SQL server (with the exception of modifications made to the row version store in tempdb). All database modification operations are logged. They can be minimally logged in bulk-logged and simple recovery models if they meet all the criteria, but all operations are always logged to some degree.
Myth 3: In simple recovery the transaction log won't grow.
The transaction log can grow for a number of reasons, even in simple recovery model. It's true that the log is truncated on each checkpoint operation, but that truncation is not guaranteed to release space. If there's say a long-running open transaction the log can't be truncated until the transaction commits or rolls back and hence may grow. There are other things that can also cause the log space not to be reused.
Myth 4: You have to switch from simple recovery to bulk-logged recovery model to get minimally logged operations.
Anything that's minimally logged in bulk logged is also minimally logged in simple recovery. If a database is in simple recovery there's no reason to switch to bulk-logged recovery to get operations to minimally log. It doesn't make more operations minimally log and now there's the requirement to take log backups (assuming a full backup has been taken to properly switch the database to bulk-logged recovery).
Myth 5: You have to take a full backup after switching from bulk-logged recovery to full recovery
You don't have to take any backups. Since the difference between full and bulk-logged is just about what operations are minimally logged, no backup is required to switch over. That said it's a very good idea to take a log backup after switching back to full recovery so that the current log interval does not contain any minimally-logged operations that could prevent tail-log backups or point-in-time recovery.
Additional references
- http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%282830%29-BULK_LOGGED-recovery-model.aspx
- http://www.sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-GAM-SGAM-PFS-and-other-allocation-maps.aspx
- http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
Acknowledgements
Thanks to Wayne and Remi for their assistance with proofreading of this article.
