Replication and Log Size problems

We have our main database which is about 60 GB now. I
typically have the log size at 1 GB.
The DB is replicating two publications to two different
servers.
It appears that re-dexing is causing our log file to grow
too large and then the replication log reader cannot read
it.
In order to get the log down to normal size again, I have
to do the following. Kill one of the publications
because two log readers are reading the log.
1) EXEC sp_repldone
2) BACKUP LOG Prod_iVend
WITH TRUNCATE_ONLY
EXEC sp_repldone
Jay,
to remove the inactive part of the log you can backup the log, assuming the
logreader has read the records. So, to reduce the log size I'd increase the
frequency of your log backup. You shouldn't have to remove the publication
to be able to reduce the log file size. In what way is the log reader
failing. If it is a timeout, you can increase the -QueryTimeOut value. You
might also consider fixing a value for the -ReadBatchThreshold.
HTH,
Paul Ibison
|||It appears that re-indexing is causing the log to grow so
much. We have
broken up reindexing so that only a few large tables are
done each night
to minimize the impact.
What happens is that the log grows to a size that the log
reader cannot
suck it in and then we get these erros.
The process could not
execute 'sp_repldone/sp_replcounters' on '(server
name)'.
Another log reader is replicating the database.
(Source: (server name) (Data source); Error number: 18752)
The process could not set the last distributed transaction.
(Source: (server name) -(job name) (Agent); Error number:
22017)
Because there are two publications coming from the DB, it
can only be
fixed by removing one of them completely. I cannot shrink
the log until
I do that.
Any ideas?

>--Original Message--
>Jay,
>to remove the inactive part of the log you can backup the
log, assuming the
>logreader has read the records. So, to reduce the log
size I'd increase the
>frequency of your log backup. You shouldn't have to
remove the publication
>to be able to reduce the log file size. In what way is
the log reader
>failing. If it is a timeout, you can increase the -
QueryTimeOut value. You
>might also consider fixing a value for the -
ReadBatchThreshold.
>HTH,
>Paul Ibison
>
>.
>
|||Assuming nobody is running sp_replcmds in QA, then the other log reader
being referred to is the other transactional publication. Try stopping one
log reader and set the other one off, so as to stagger the processes. Once
one has finished reading, run the pther then backup and shrink.
Also change the 2 parameters mentioned earlier.
HTH,
Paul Ibison
|||whenever you run sp_replcmds yourself you MUST run a sp_replflush before running it or you will get errors like this.
The only clean way of fixing the multiple log reader agent problem is to stop the SQL Server agent, check task manager to see all the logread.exe's go away. Then restart the SQL Server agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" wrote:

> Assuming nobody is running sp_replcmds in QA, then the other log reader
> being referred to is the other transactional publication. Try stopping one
> log reader and set the other one off, so as to stagger the processes. Once
> one has finished reading, run the pther then backup and shrink.
> Also change the 2 parameters mentioned earlier.
> HTH,
> Paul Ibison
>
>
|||With what I am seeing, that is not possible. If I stop the
log reader agent for one, it stops for log reader for the
other one automatically. I think they are the same log
reader, even though they are two different publications.

>--Original Message--
>Assuming nobody is running sp_replcmds in QA, then the
other log reader
>being referred to is the other transactional publication.
Try stopping one
>log reader and set the other one off, so as to stagger
the processes. Once
>one has finished reading, run the pther then backup and
shrink.
>Also change the 2 parameters mentioned earlier.
>HTH,
>Paul Ibison
>
>.
>
|||I think the real problem is that the Log file is just too
big. I have seen it expand to 14 GB and then the log
reader agent just chews on it forever. It is a 4 GB ram
server with 3 GB allocated for SQL Server.
Is there any BK article on this being an issue? I cannot
find anything.

>--Original Message--
>whenever you run sp_replcmds yourself you MUST run a
sp_replflush before running it or you will get errors like
this.
>The only clean way of fixing the multiple log reader
agent problem is to stop the SQL Server agent, check task
manager to see all the logread.exe's go away. Then restart
the SQL Server agent.[vbcol=seagreen]
>--
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>
>"Paul Ibison" wrote:
other log reader[vbcol=seagreen]
publication. Try stopping one[vbcol=seagreen]
the processes. Once[vbcol=seagreen]
shrink.
>.
>
|||This doesn't sound correct. The log reader agent is intelligent enough to know the LSN (log sequence number) it last read and marked as replicated, and then starts reading from there
Issue a sp_repltrans to get an idea of how many transactions remain unread in the log.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jay" wrote:

> I think the real problem is that the Log file is just too
> big. I have seen it expand to 14 GB and then the log
> reader agent just chews on it forever. It is a 4 GB ram
> server with 3 GB allocated for SQL Server.
> Is there any BK article on this being an issue? I cannot
> find anything.
>
> sp_replflush before running it or you will get errors like
> this.
> agent problem is to stop the SQL Server agent, check task
> manager to see all the logread.exe's go away. Then restart
> the SQL Server agent.
> other log reader
> publication. Try stopping one
> the processes. Once
> shrink.
>
|||there seems to be another bug here. when you create two publications in the
same database there are actually two log reader agents created. Only one log
reader agent runs though - that is the later one.
However check in the mangement folder and you will see two log reader agent
jobs for this publication database.
The bug is if you run a sp_helppublication for your publications each one
will report the name of the log reader agent which they created.
The problem/bug is, if that sp_helppublication should be reporting the log
reader agent in use.
It is possible that you have started two instances of the same log reader,
or you have started two log readers for two publications in the same
publication database. This is only possibly if you try to start it from the
management folder or start the jobs manually.
Again, bounce SQL Server agent to correct the problem.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:208e701c45946$0276f630$a401280a@.phx.gbl...[vbcol=seagreen]
> With what I am seeing, that is not possible. If I stop the
> log reader agent for one, it stops for log reader for the
> other one automatically. I think they are the same log
> reader, even though they are two different publications.
>
> other log reader
> Try stopping one
> the processes. Once
> shrink.