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...
>> 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
>|||"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.
>|||This is a multi-part message in MIME format.
--000900060703070102060205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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...
>
>>"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
>>
>
>
--000900060703070102060205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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).<br>
<br>
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).<br>
<br>
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.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tim Greenwood wrote:
<blockquote cite="mid%2310nr4WaGHA.4780@.TK2MSFTNGP02.phx.gbl"
type="cite">
<pre wrap="">Well, that's easy....HELL no and no...
Thanks that was quite simple. Doh
"David Browne" <davidbaxterbrowne no potted <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:meat@.hotmail.com">meat@.hotmail.com</a>> wrote in
message <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl">news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl">news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<pre wrap="">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
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--000900060703070102060205--