Search

Change Default Recovery Model by way0utwest

Closed
as Won't Fix Help for as Won't Fix

31
5
Sign in
 to vote
Type: Suggestion
ID: 472969
Opened: 7/7/2009 8:17:13 AM
Access Restriction: Public
1
Workaround(s)
There is a constant battle from many people to handle the transaction log size. I see questions posted online on an almost daily basis about how to deal with a large transaction log from many product users that are not experienced DBAs. They often set up full backups, assuming that this will clear the log.

The default setting for model (and thus other databases) is the full recovery model. While this is what most people need, they do not know how to deal with it.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

My solution is that the default recovery model for all databases be set to simple. This will allow those users who are aware of how to manage the transaction log to change it, but all other users, especially Express users, a simpler environment that they can just set up full backups for.

Benefits

Improved Installation
Improved Performance
Improved Administration
Improved Reliability

Other Benefits

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 11/14/2011 at 4:13 PM
Having thought about this for some time, I've come to the conclusion that the existing behavior is so well established that it would cause significant issues to change it at this point.
While it would make life simpler for some customers, it would cause problems for others, which doesn't seem like justification for changing it.

Posted by Martin Smith on 1/1/2011 at 7:01 PM
Not sure about this request. An equally common problem I see posted online is from people that have deleted data and have never taken a full backup so their database is in auto-truncate mode, I think "friendlier" defaults would be full recovery model and a doing away with the requirement for a full backup for these to become useful in the first place.
Posted by tanoshimi on 9/6/2009 at 9:44 AM
I particularly like the proposal that Simple be default for SQL Server Express installations. A lot of people I know use Express installations for development work, e.g. on laptops, which they always set to Simple recovery model. It's a pain at the moment to change the recovery model every time you create a new database.
Having said that - i agree with the arguments that simple recovery should be the default for *all* versions...
Posted by TiborK on 8/28/2009 at 9:39 AM
I fully agree. If you have it "the wrong way around":

Full when you should have simple. This is what many customers experience today, end result is huge ldf files and people deleting ldf files and such horrid things.

Simple when you should have full. I.e., want to do log backups. End result is that you will notice this mistake pretty quickly (the first time the log backup job is executed). Close to immediate feedback and easy to fix.
Posted by sskaar07 on 7/24/2009 at 7:38 AM
I like this idea, I would also recommend upgrading the create database wizard to inform users of the recovery model choices and that the affects thereof.
Posted by Microsoft on 7/8/2009 at 10:14 AM
Hi Aaron
This does sound like a good idea.
I'm currently doing due dilligence by getting in contact with the people who made the original decision to make sure I'm not missing something significant here. Assuming that I'm not, I'll push this DCR forward.

Kevin Farlee
Posted by AaronBertrand on 7/7/2009 at 9:15 AM
For more elaborate background, I blogged about this recently:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/06/21/why-is-disaster-recovery-an-afterthought.aspx
Sign in to post a workaround.
Posted by Andreas_Wolter on 4/15/2011 at 8:04 AM
in fact: when a database in full recovery mode has never been log-backed up, the behaviour now is like simple, also calles "pseudo-simple"
see heere for Paul Randalls script:
http://www.sqlskills.com/BLOGS/PAUL/post/New-script-is-that-database-REALLY-in-the-FULL-recovery-mode.aspx
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement