Re: Log file...

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 07/27/04 

Date: Tue, 27 Jul 2004 15:43:26 -0600

Hi Greg,
Are you backing up the log file? You want to do regular
backups of the log - my first guess would be that you
probably aren't doing those. When you backup the log, it
truncates (which is not the same as physically shrinking)
the log. It just means that parts of the log get marked as
inactive so these parts (virtual log files or VLFs) can be
used to record future transactions. Otherwise, the log just
keeps growing to record the transactions and nothing is
"reused" or overwritten.
Another option is if the transaction log is not part of your
recovery plan, you can set the database to simple recovery
model although this isn't typically used with production
databases.
If you need to get the log down to a more reasonable size,
you can use dbcc shrinkfile. You'd want to shrink it to the
size it needs to be for your database as continual
shrinking, growing of the log consumes resources. You can
find info on shrinking the log in the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318

-Sue

On Tue, 27 Jul 2004 14:21:04 -0700, "Greg"
<Greg@discussions.microsoft.com> wrote:

>Hi everyone!
>I have a database sized about 500MB and it used to have about 2-300MB log file. But now the log file is about 4GB and I don't know when it grew up that big. I don't have any issues now, but is it OK or is there something I can do to make it smaller? Thank you for your time.
>
>Greg

Relevant Pages

  • 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)
  • 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)
  • Re: Cant shrink transaction log
    ... Shrinking the Transaction Log in SQL ... Server 2000 with DBCC SHRINKFILE" is what I was looking ...
    (microsoft.public.sqlserver.server)
  • Re: Shrink transaction log after truncate table?
    ... here are my opinions on shrinking: ... What about the "Auto Shrink" option for a sql server database, would that be good, or just a waste ... > I'm looking for a good way to shrink/compress this transaction log after I ...
    (microsoft.public.sqlserver.programming)
  • Re: How to shrink log file.
    ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ... I have a database in SQL 2000 server whose ...
    (microsoft.public.sqlserver.server)