Re: Investigating what makes my log file grow and grow and grow

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 10/30/04 

Date: Fri, 29 Oct 2004 22:40:24 -0400

When you get a value for DBCC Opentran Oldest non-distributed LSN it means
there are transaction in your tlog that the log reader has not read yet, or
has not marked as replicated.

There is a problem with your log reader. I can't tell you what it is,
perhaps there is an error message on it. Perhaps your system is under load
and not enough resources are avaliable for the log reader - this is only
solved by a reboot.

I would never run sp_repldone the way you are. Basically what you are in
effect doing is saying I need all commands replicated for a time period, and
then when I run sp_repldone if there are any commands in the log, throw them
away.

It could also be that you are not dumping your log frequently enough. You
should be dumping it very frequently, perhaps every 20 minutes, or even
every 1-3 minutes if your database is large.

Pruning your tlog this way will improve overall system performance, and mean
your log reader has less log to read, hence its read performance will be
better. You might want to decrease its PollingInterval to 1s.

Also run a dbcc loginfo. Ideally you should have a small number of rows
which correspond to your virtual log files, ie 16 or so. Best of all 4. If
you have a large number you should try to size your tlog to something large
to prevent frequent autogrows.

Another factor which could cause your log to grow is orphaned transactions.
DBCC opentran should show this. 

-- 
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Roger Denison" <anonymous@discussions.microsoft.com> wrote in message
news:19be01c4bdd2$48a792e0$a401280a@phx.gbl...
> I need to learn the steps to investigate which
> transactions are not getting replicated, thus causing my
> log file to grow.  I periodically check the dbs I
> administer and wait until after hours to force
> replacation done using sp_ReplDone, followed by a backup
> and shrink, for those dbs with excessively large log
> files.  I only have this problem on the server that acts
> as distributor and subscriber.
>
> I even set up jobs that do the following, in order (by
> start time):
> 1)exec sp_ReplDone @xactid = NULL, @xact_segno = NULL,
> @numtrans = 0, @time = 0, @reset = 1
> 2)Backup transaction log (part of maintenance plan)
> 3)Backup db (part of maintenance plan)
> 4)DBCC Shrinkfile
>
> I have these jobs spread out every 1/2 hour to give them
> plenty of time to complete before the next one begins.
> Yet the log file doesn't shrink.
>
> When I run DBCC OPENTRAN on the db, I get the following:
> Transaction information for database 'Database1'.
>
> Replicated Transaction Information:
>         Oldest distributed LSN     : (0:0:0)
>         Oldest non-distributed LSN : (570647:21697:1)
> DBCC execution completed.
>
> So, my two requests are:
> 1) why doesn't my shrink procedure work? (If I do these
> steps by hand I can shrink the log file down to 1MB)
> 2) how do I determine what's causing the hangup that
> starts the log file to grow.
>
> Roger.

Relevant Pages

  • Re: Transaction logs
    ... In Simple mode the transaction log will automatically truncate when it gets ... That means that the space in the log file of any transactions ... long as there are no open transactions in the way. ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Slow performance after restarting
    ... Detach/Rename/Re-attach gets me a much smaller transaction ... log (these are all dev boxes, yes), but doesn't seem to affect the behavior. ... > log file is this size or what you do for backups. ... >> DBCC SHRINKDATABASE and DBCC SHRINKFILE don't seem to affect the ...
    (microsoft.public.sqlserver.programming)
  • Re: the file writing consistency About Windows NTFS
    ... "NTFS uses the transaction processing moidel to implement its file system ... Nesting does not occur - if the log file runs out of space ... > 2.In order to recover those files which are destoried, ...
    (microsoft.public.vc.mfc)
  • Re: Shrink the log file
    ... Until there have been enough transactions to fill up a virtual log file, ... > I have a database with 1.5GB log file.After the transaction backup, ... tried to perform the SHRINKFILE command but I got an error message as below: ...
    (microsoft.public.sqlserver.server)
  • Re: dbcc opentran is not helping me on "waiting for backend connection"
    ... Basically this is telling you that the log reader has transactions to write ... I think your problem is with depleted buffers on SQL Server. ... The publisher has no blocking and was ... > at least start going 1 transaction at a time. ...
    (microsoft.public.sqlserver.replication)
Loading