10-04-2011 05:37 AM
The way my SQL Server is set up today is with Full Recovery Model for all database. This generates a tremendous amount of transaction log data, where the log file can easily be in the gigabyte range for a small, 50K database.
I was thinking about changing the Recovery Model to Simple, thus decreasing the amount of data. Before I do that, however, I would like to know if this is safe or not.
Can anyone shed some light on this, please?
Remember: wherever you go, there you are.
10-05-2011 11:40 PM
From Sql Server documentation, which supply detailed considerations which and when to choose each one of the Recovery Models:
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.
To decide upon the optimal recovery model for a particular database, you should consider both the recovery goals and requirements for the database and whether you can manage log backups.
Identifying Recovery Goals and Requirements
To select the best suited recovery model for a database, consider the recovery goals and requirements for the database. Answering the following questions can help you determine the availability requirements and the sensitivity to data loss.
- How important is it to never lose a change?
- How easy would it be to re-create lost data?
- Do you have two or more databases that must be logically consistent?
If this is so, consider using Microsoft Distributed Transaction Coordinator (MS DTC) transactions. For more information, see MS DTC Distributed Transactions.
Under the full recovery model, if transactions have been marked in each of the related databases, you can recover the database to a consistent point. This requires restoring each of the databases with the same transaction mark as the recovery point for each. However, using a mark for a recovery point loses any transactions committed after that point. For more information, see Using Marked Transactions (Full Recovery Model).
Does your organization use system or database administrators? If it does not, who will be responsible for performing backup and recovery operations, and how will the individuals be trained?
Data Usage Patterns
For each database, consider the following questions:
- How frequently does the data in the database change?
- Are some tables modified significantly more frequently than other tables?
- Are there critical production periods? If there are, what are the usage patterns during these periods? Does the database experience peak periods for insert and other update operations?
You might want to schedule data backups to occur during off-peak hours. When the I/O system is under heavy use, typically, only log backups should be used.
- Is the database subject to risky updates or application errors that may not be detected immediately?
If the database is, consider using the full recovery model. This lets you use log backups to recover the database to a specific point in time.
When to Use the Simple Recovery Model
Use the simple recovery model if the following are all true:
- 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.
For more information, see Backup Under the Simple Recovery Model.
When to Use the Full Recovery Model
Use the full recovery model and, optionally, also the bulk-logged recovery model if any one of the following is true:
- You must be able to recover all the data.
- If the database contains multiple filegroups, and you want piecemeal restore of read/write secondary filegroups and, optionally, read-only filegroups.
- You must be able to recover to the point of failure.
- You want to be able to restore individual pages.
- You are willing to incur the administrative costs of transaction log backups.
For more information, see Backup Under the Full Recovery Model.
When to Use the Bulk-Logged Recovery Model
The bulk-logged recovery model is intended strictly as an adjunct to the full recovery model. We recommend that you use it only during periods in which you are running large-scale bulk operations, and in which you do not require point-in-time recovery of the database.
- Is the database subject to periodic bulk operations on the database?
Under this recovery model, most bulk operations are only minimally logged. If you use the full recovery model, you can switch temporarily to the bulk-logged recovery model before you perform such bulk operations. For information about what operations are minimally logged under the bulk-logged recovery model, see Operations That Can Be Minimally Logged.
Generally, the bulk-logged recovery model resembles the full recovery model, except that it minimally logs most bulk operations. A transaction log backup captures the log and, also, the results of any minimally logged operations that have completed since the last backup. This can make the log backups very large. Therefore, the bulk-logged recovery model is intended only for use during bulk operations that allow for minimal logging. We recommend that you use the full recovery model the rest of the time. As soon as a set of bulk operations finishes, we recommend that you immediately switch back to the full recovery model.
For more information about the bulk-logged recovery model, including its restrictions, see Backup Under the Bulk-Logged Recovery Model.
For more information about the restore operations that are supported by the different recovery models, see Restore and Recovery Overview (SQL Server).
10-05-2011 11:55 PM
So this explains about the different options, and I appreciate the clarification. Thanks!
There is one "but" though: are there any specific requirements on the recovery model from HP? Since the databases are created with the Full Recovery Model one could assume that this is done on purpose, i.e., there is a requirement from the application on this model.
On the other hand, this might well be just a matter of going with the default and an enlightened choice of another model might not be amiss.
Hence a followup question: should I (or should I not) switch all my databases to Simple Recovery Model, and, if so, is there a default that I can change in SQL Server that lets me set all subsequent databases created by QC to Simple Recovery Model?
(Who said running a QC installation was easy...)
Remember: wherever you go, there you are.
10-09-2011 06:22 AM - edited 10-09-2011 06:24 AM
Words by ALM R&D DBA:
There is no specific recommendation for ALM/QC about the recovery model, than any other DB related application.
Understandably, it is preferrable to have to safest recovery model to be used, which is full model, in order to prevent any data loss from any of ALM Projects.
But like in any other application, one should do the math by putting all relevant considerations and decide how much the project's database data is volatile on the one hand and its changes are critical on the other hand. Based on the considerations given, one should decide.
For example, for non-active projects, where data is almost not being modified in their database, there is a place to consider the change their DB recovery model.
On the other hand, it wouldn’t be advisable to do so on the most active and therefore recent and volatile projects.
The simple recovery model is easier to manage than the other two models but this comes at the expense of greater data loss exposure if a data file is damaged. All changes since the most recent database or differential database backup are lost and must be manually reentered.
The default recovery model is determined by the recovery model of the model database, even though it would be advisable to be changed from the default.