Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the Simple Recovery model.

Quite often and for certain practical reasons many databases are created using SSMS. However mistakes can be made and the operator can forget to specify the Simple Recovery model. This leads to a "suprise" a few days later when the box is struggling with disk space due to three or four 60GB log files that have never been truncated.

I can make the Simple Recovery model the default setting for new databases by configuring the recovery model on the model database. However, is this recommended, if I do this could it come back and bite me in any way in the future?

share|improve this question

3 Answers 3

up vote 17 down vote accepted

I see one of three options here:

1) you can have a templated script to create databases that explicitly includes the recovery model.

2) you can set the model database to simple and not have to worry about this.

3) you can hope everybody remembers, which seems like what you are doing. (not recommended)

I would personally go with number two. That's what the model database is there for.

share|improve this answer
    
I concur with # 2 and follow this practice. Further, if you find yourself in an organization that lets anybody create anything on a DEV database server, this keeps the anybody's from impacting themselves or others. –  jl01 Sep 28 '11 at 15:44
1  
#2 is the way to go here. –  mrdenny Apr 9 '12 at 18:13

The safe bet is putting your DB in full mode, but then you have the log growth issue. Now there are some options:

  • Put a limit on the max size of your log file. This will affect operations once this limit is hit. The benefit is that it will prevent scenarios where the disk runs out of space and then you will have bigger issues.
  • Create alerts that fire once your log file grow above a watermark and then manage it.
  • Schedule log shrink jobs. Log shriking hurts the performance of your DB. I would not suggest it.

Being the DBA you should use all options that helps with a recovery. It also depends on your SLA with the business.

All that being said, I manage a few DBs in simple mode. This is because of disclaimers outlined in the SLA. The business decided on not spending on drives for the log files (you can take a horse to the water, but you can't make it drink). The business manages the backup, restore, and DR. There was a DR, and the money lost was greater than what it would have cost in extra disk space.

share|improve this answer
2  
This answer disregards what the user wants to do. As for your points: 1 is valid; 2 add more detail. Also to manage log size, you have to take log / full backups; 3 you'll never be able to shrink the log files unless you back them up to free up space. As for performance, the shrink only hurts when the log file grows again. Log files behave differently than data files. –  Eric Humphrey - lotsahelp Sep 28 '11 at 16:18
    
Setting a max size on your transaction log is usually a pretty bad plan as this will cause an outage without the disk being full. #3 is an awful idea which shouldn't have even been brought up. –  mrdenny Apr 9 '12 at 18:13

Adding to @Surfer513

4) Policy-Based Management policy either to enforce the simple recovery model, or at most let you know when a DB is not

Although I favor setting model to simple this does not prevent the T-SQL command from being used and setting it to something else. You can use a policy to evaluate if the recovery model is not Simple and opt to have the policy change it for you.

This MSSQLTip.com article is on checking for Full, but you can easily just have yours check for Simple. You can also throw in a check to see if a backup has ever occured on the database too.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.