|
|
Posted by Hugo Kornelis on 10/02/33 11:56
On 24 Aug 2006 07:24:43 -0700, yashgt@gmail.com 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
Navigation:
[Reply to this message]
|