Re: Transaction Log Filling Up - SQL 2000

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 05/05/04 

Date: Tue, 4 May 2004 22:05:28 -0500

The best solution is to issue a log backup to cleanly backup the
transactions and truncate the inactive portion of the log. There should be
no need to shrink the log file. If it wasn't large enough to handle an
ALTER table it probably wasn't large enough to begin with. 

--
Andrew J. Kelly  SQL MVP
"bhp" <dbaqueen2000@yahoo.com> wrote in message
news:2615daf8.0405041619.16c32b9c@posting.google.com...
> I am really confused and hope someone can help me out.  I have a
> script that will be distributed to our customer base in an upgrade of
> our software.  This script is ALTERing many tables to ADD a column
> with a DEFAULT.  Some of these tables have hundreds of thousands
> records.  When I am running the script the Transaction Log has been
> filling up on some customer dbs.  We decided to issue a CHECKPOINT
> after the ALTER immediately followed by SHRINKFILE of the Trans Log.
> Is this a feasible solution?  Or would it be better to TRUNCATE the
> LOG after each ALTER?  Or am I totally missing the boat here?  Any
> suggestions would be greatly appreciated.

Relevant Pages

  • Re: Should Trans-Log be on its own RAID volume?
    ... I thought Tran log gets truncated at checkpoint no matter how full its is, ... it gets rid of committed transactions ... > mode it will truncate when the log gets about 70% full and will only ... It's not the size but the volume and way the transactions get ...
    (microsoft.public.sqlserver.server)
  • Re: yet another out of control log file
    ... A FULL backup does not truncate the log, ... Truncating the log (either manually or through a log backup) will not shrink ... SQL Server 2000 SP3. ...
    (microsoft.public.sqlserver.server)
  • Re: Log Question
    ... Perhaps I wasn't clear enough in my previous message I'm trying to explain that you can backup the log (with truncate_only or not) but you shouldn't truncate the physical log file because you will have problems if you do that. ... could I possibly blow away transactions that ...
    (microsoft.public.sqlserver.replication)
  • RE: Lights needed on the T Log management
    ... "Chris V." wrote: ... > Transations are kept in the log until the log is or backed up or truncate, ... > The size will be reduce will DBCC SHRINKxxx ... > append to the log which will keep growing, or will the new transactions be ...
    (microsoft.public.sqlserver.server)
  • Re: Object creation not appearing in consequent log backup
    ... > DBCC OPENTRAN --- no open transactions exist ... > Ran the log backup job on primary, copy to standby and retored log. ... >> transactions that may affect it. ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.programming)