Home Dashboard Directory Help

Change Default Recovery Model by way0utwest


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

Sign in
to vote
Type: Suggestion
ID: 472969
Opened: 7/7/2009 8:17:13 AM
Access Restriction: Public


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.
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:

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: