Schedule for Backing up logs

How does one determine a reasonable interval for backing up transaction
logs? For example, we have one small db that is only 1.8GB and the
transaction log is consistently around 1GB even after backup. I suppose I'd
need to add the "truncate" option to make it shrink? We only get really
busy about 1/2 of the week but right now our DBA backs up tlogs once daily
with a full backup weekly. Is this really sufficient?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> How does one determine a reasonable interval for backing up transaction
> logs? For example, we have one small db that is only 1.8GB and the
> transaction log is consistently around 1GB even after backup. I suppose
> I'd need to add the "truncate" option to make it shrink? We only get
> really busy about 1/2 of the week but right now our DBA backs up tlogs
> once daily with a full backup weekly. Is this really sufficient?
In case of a disaster is recovering to the beginning of the day acceptable?
In case of a disaster, is the recovery time to restore the weekly full and 5
days of transaction logs acceptable?
David|||Well, that's easy....HELL no and no...
Thanks that was quite simple. Doh
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> In case of a disaster is recovering to the beginning of the day
> acceptable?
> In case of a disaster, is the recovery time to restore the weekly full and
> 5 days of transaction logs acceptable?
> David
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> How does one determine a reasonable interval for backing up transaction
> logs? For example, we have one small db that is only 1.8GB and the
> transaction log is consistently around 1GB even after backup. I suppose
I'd
> need to add the "truncate" option to make it shrink?
No, you would need to do a DBCC shrinkfile.
I would highly suggest not to. If your log keeps growing to 1 gb between
backups, that's basically what you need. Leave it at that.
If you keep shrinking it, you run the risk of getting disk level
fragmentation.

> We only get really
> busy about 1/2 of the week but right now our DBA backs up tlogs once daily
> with a full backup weekly. Is this really sufficient?
Depends, what's your disaster recovery plan call for? Can you live with
losing a day's worth of data?
I have one system where we do transaction log backups every 20 minutes,
another we just keep the db in simple mode. Depends on your needs/desires.

>|||Well, what's an acceptable data loss window? Whatever it is, that
becomes your new tlog backup frequency. For example, if losing the last
30 minute's worth of data is acceptable (no data loss is desirable but
you've got to be realistic about it) then do transaction log backups
every 30 minutes. That will almost certainly keep the size of the log
file down (or rather it will use less of the log file and so if you're
tight on disk space it'd be OK to shrink it down to cater for about 30
minutes worth of transactions).
If the recovery time of restoring a full backup & up to 5 daily tlogs is
unacceptable then you may want to think about slipping in regular
differentials. For example, if you do a full weekly backup, a daily
differential and half-hourly log backups then the recovery time would be
the time it takes to restore the last full backup, the most recent
differential and every log backup done since then (which would be up to
48 log backups, but they're be much smaller than your current log
backups because they're only 30 minutes worth of changes rather than 24
hours worth of changes). The differentials will make it possible to
essentially skip ahead to a much more recent recovery point without
having to apply all the log backups since the full backup, which will
speed up the recovery time. If that's still too long, then maybe you
should do nightly full backups with the half-hourly log backups (and
maybe even slip in a differential every couple hours).
It's basically just a case of juggling the DB, diff & log backups to
cover your maximum acceptable data loss window and maximum acceptable
recovery time. The more often you do log backups the smaller those log
backups will be and hence the less physical log file you'll need to
store those transactions between log backups.
*mike hodgson*
http://sqlnerd.blogspot.com
Tim Greenwood wrote:

>Well, that's easy....HELL no and no...
>Thanks that was quite simple. Doh
>"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>message news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl...
>
>
>