Investigating what makes my log file grow and grow and grow

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.
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.