Re: Transaction log keeps growing

On 24 Aug 2006 07:24:43 -0700, yashgt@xxxxxxxxx wrote:

Hi,

We have created a SQL server 2000 database. We observe that the
transaction log keeps growing over time. We are now about to run out of
space. We have been periodically shrinking the database. Nevertheless
the size has increased. I would imagine that a transaction log can be
eliminated if we stop the database. Can that be done? Is there a way to
completely wipe off the transaction log?

Thanks,
Yash

Hi Yash,

Don't follow Jack's advice. This process is not completely fail-safe,
AFAIK, and not needed either. Besides, it would only address the
symptom, not the cause.

First, decide the amount of data loss your application can bear. Then
set the right options to ensure that the transaction log doesn't grow
endlessly. These options depend on how much data you're prepared to lose
in the event of a disaster.

If you're satisfied with the ability to restore the last full or
differential backup and lose changes made sinice then, the only thing
you have to do is change the recovery model to "simple". Once that is
done, the transaction log will stop growing and start reusing existing
space instead. See below for how (and if!) to shrink it.

If you can't afford to lose data but need the ability to restore to the
moment in time just before the disk crashed or you accidentally dropped
the orders table, you'll have to use the "full" recovery model (the
default). But you'll also have to schedule regular transaction log
backups, since full recovery prevents transaction log data from being
overwritten until it has been backed up. After scheduling log backups,
the transaction log will stop growing and start reusing existing space
instead. See below for how (and if!) to shrink it.

You might consider shrinking the transaction log. But you can also keep
it as it is, if you don't need the disk space. Be aware that after
shrinking the file, SQL Server will have to grow it (using autogrow)
back to it's normal working size. Autogrow is slow, and always kicks in
when your most critical process is executing. Shrinking a database or a
log should reallly only be done when something has caused it to grow
well beyond it's normal size, and only if you really have to reclaim the
disk space. For more information on how shrinking works, why you should
use it only sparingly and how to do it, read Tibor's article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • RE: Shrinking a transaction log file
    ... Going to answer in two parts one on database and one on transaction logs. ... Transaction Log Shrinking ... When you do a transaction log backups the inactive parts of the ... If I needed to restore an old backup that has shrunk, ...
    (microsoft.public.sqlserver.server)
  • Help in transaction log growth
    ... I have too many deletes in the tables in a database and that log is ... growing in a big way. ... the transaction log. ... I tried the shrink DBCC command but it shrinks to 9 GB if the file ...
    (microsoft.public.sqlserver.programming)
  • Controlling log file size
    ... Shrinking the Transaction Log in SQL Server 2000 with ... Also do a frequent transaction log backups to over come ... You can also turn the Database from FULL recovery ... I backed up the log file and shrink the database ...
    (microsoft.public.sqlserver.server)
  • Re: Shrinking a transaction log file
    ... set the size of a database to 40MB, then if the database grows in ... When shrinking the database what does that really mean? ... to make sure that the space in the log file is re-usable. ... normally should not need to shrink the transaction log unless you do ...
    (microsoft.public.sqlserver.server)
  • Shrinking a logfile -
    ... best procedure' to shrink logfiles when they grow too large. ... Does the transaction log backup process somehow modify/affect the related ... Your assistance with improving my method of shrinking logfiles would be ...
    (microsoft.public.sqlserver.setup)