Prev: Reporting Services subscription permissions issue?
Next: SQL Server 2008 64bit services fail to start
From: Coop on 15 Apr 2010 13:02
In general, is there any harm in making all databses Simple recovery other
than losing ability to recover point-in-time? Do some products require that
their databases are in Full mode for some internal maintenence proceses to
work? I have about 30 production databases on a SQL Server 2005 Standard
server. I'd like to switch all DBs to Simple Recovery model as I'm tired of
managing tranlog growth and backups. The DBs vary in size from 10MB to 3GB.
I don't necessarily need point-in-time recovery for any of them. I can manage
user expectations that I can recover to last full backup as of previous
night, or for small DBs, I can take multiple full backups during the day.
Some DBs are already in Simple mode, as I think some product installations
probably installed their DBs in Simple mode. Thanks for your feedback.
From: Tibor Karaszi on 15 Apr 2010 13:41
> I can manage
> user expectations that I can recover to last full backup as of previous
> night, or for small DBs, I can take multiple full backups during the day.

If you really, really feel confident with above, then go ahead. I've never
heard about any software that would require full recovery model. In fact,
the recovery model was introduced (in 2000, compared to what we had in
earlier versions) to do just that: make the backup-option setting not affect
application code.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Coop" <Coop(a)discussions.microsoft.com> wrote in message
news:8B580AFC-F1AB-49F0-8728-0447723E06AB(a)microsoft.com...
> In general, is there any harm in making all databses Simple recovery other
> than losing ability to recover point-in-time? Do some products require
> that
> their databases are in Full mode for some internal maintenence proceses to
> work? I have about 30 production databases on a SQL Server 2005 Standard
> server. I'd like to switch all DBs to Simple Recovery model as I'm tired
> of
> managing tranlog growth and backups. The DBs vary in size from 10MB to
> 3GB.
> I don't necessarily need point-in-time recovery for any of them. I can
> manage
> user expectations that I can recover to last full backup as of previous
> night, or for small DBs, I can take multiple full backups during the day.
> Some DBs are already in Simple mode, as I think some product installations
> probably installed their DBs in Simple mode. Thanks for your feedback.

From: Jeffrey Williams on 15 Apr 2010 22:42
I would agree with Tibor - if you are confident that you can live with a
loss of up to 30+ hours of data then change them. However, I am concerned
with your reasons for wanting to do so. I do not understand what kind of
issues you are having managing transaction logs. Once you setup your
transaction log backups - and back up your transaction logs on a frequent
basis (every hour would be a good start), the log file will settle at the
largest size it needs to be in that hour time frame.

Honestly, it sounds to me like you are not performing any transaction log
backups and instead are truncating the log and shrinking it on a regular
basis. This is a bad idea for a lot of reasons, but if that is what you
are doing then setting to simple accomplishes the same thing.

And finally, your convenience should not determine the business requirements
for restoring the system. That decision is entirely the responsibility of
the business owners of the data.

Good luck,

Jeff

"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:#ssPSLM3KHA.556(a)TK2MSFTNGP04.phx.gbl...
>> I can manage
>> user expectations that I can recover to last full backup as of previous
>> night, or for small DBs, I can take multiple full backups during the day.
>
> If you really, really feel confident with above, then go ahead. I've never
> heard about any software that would require full recovery model. In fact,
> the recovery model was introduced (in 2000, compared to what we had in
> earlier versions) to do just that: make the backup-option setting not
> affect application code.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "Coop" <Coop(a)discussions.microsoft.com> wrote in message
> news:8B580AFC-F1AB-49F0-8728-0447723E06AB(a)microsoft.com...
>> In general, is there any harm in making all databses Simple recovery
>> other
>> than losing ability to recover point-in-time? Do some products require
>> that
>> their databases are in Full mode for some internal maintenence proceses
>> to
>> work? I have about 30 production databases on a SQL Server 2005 Standard
>> server. I'd like to switch all DBs to Simple Recovery model as I'm tired
>> of
>> managing tranlog growth and backups. The DBs vary in size from 10MB to
>> 3GB.
>> I don't necessarily need point-in-time recovery for any of them. I can
>> manage
>> user expectations that I can recover to last full backup as of previous
>> night, or for small DBs, I can take multiple full backups during the day.
>> Some DBs are already in Simple mode, as I think some product
>> installations
>> probably installed their DBs in Simple mode. Thanks for your feedback.
>
From: Jay Konigsberg on 16 Apr 2010 01:03
> I'd like to switch all DBs to Simple Recovery model as I'm tired of
> managing tranlog growth and backups.

I don't understand why you need to manage these things and why it isn't just
automated. There are a ton of example scripts available. Besides, managing
backups and log files is a primary DBA duty.

--
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Coop" <Coop(a)discussions.microsoft.com> wrote in message
news:8B580AFC-F1AB-49F0-8728-0447723E06AB(a)microsoft.com...
> In general, is there any harm in making all databses Simple recovery other
> than losing ability to recover point-in-time? Do some products require
> that
> their databases are in Full mode for some internal maintenence proceses to
> work? I have about 30 production databases on a SQL Server 2005 Standard
> server. I'd like to switch all DBs to Simple Recovery model as I'm tired
> of
> managing tranlog growth and backups. The DBs vary in size from 10MB to
> 3GB.
> I don't necessarily need point-in-time recovery for any of them. I can
> manage
> user expectations that I can recover to last full backup as of previous
> night, or for small DBs, I can take multiple full backups during the day.
> Some DBs are already in Simple mode, as I think some product installations
> probably installed their DBs in Simple mode. Thanks for your feedback.


From: iNewbie on 16 Apr 2010 08:41
it depends on your bussiness needs, i have it in simple mode, i make a full
backup at night and some differential backups during the day.
I do know that Log Shipping requieres to have the database in Full Mode.

:-)

"Coop" wrote:

> In general, is there any harm in making all databses Simple recovery other
> than losing ability to recover point-in-time? Do some products require that
> their databases are in Full mode for some internal maintenence proceses to
> work? I have about 30 production databases on a SQL Server 2005 Standard
> server. I'd like to switch all DBs to Simple Recovery model as I'm tired of
> managing tranlog growth and backups. The DBs vary in size from 10MB to 3GB.
> I don't necessarily need point-in-time recovery for any of them. I can manage
> user expectations that I can recover to last full backup as of previous
> night, or for small DBs, I can take multiple full backups during the day.
> Some DBs are already in Simple mode, as I think some product installations
> probably installed their DBs in Simple mode. Thanks for your feedback.
 |  Next  |  Last
Pages: 1 2
Prev: Reporting Services subscription permissions issue?
Next: SQL Server 2008 64bit services fail to start