Replication down Log Shrinking

Hi,

Is there any relation between replication and the transaction log, we have set up transactional replication between our databases, and I had to clear the log as its size was huge, I also reduced the temp db size. After that, the replication is down, with an error, the subscriptions are invalid, reconfigure the Topology.

Is this related to the shrinking of the logs ?

These two things (shrinking the log, and the expiration of subscription) are not related.|||Ok Sounds logical, but when we have to reduce the size of tempdb, the way we do it is restart the sql server right ? In this case P2P configured has to go down. If yes, then what must be the ideal way of reducing the tempdb size on a system where P2P replication is configured ?|||

. Transaction log and Transactional replication very much related.

Transaction Log Space

For each database that will be published in transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database may require more space than the log of an identical, unpublished database. This is because the log records may not be purged until they have been moved to the distribution database.

If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been passed into the distribution database (unless replication is turned off completely for that database). It is recommended that you set the transaction log file to autogrow so that the log can accommodate these circumstances.

Ref :

http://msdn2.microsoft.com/en-us/library/aa237475(SQL.80).aspx

http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx

Madhu

|||

see the first thing i want to mention here is you should never ever trucate the TL of a replicated DB. Each subscription has a lif which is by default (336 hrs ) if i remember correctly, which can be changed in the property of of a publication. If you restart server the replication will not effect. it will re-start from where it left.

Madhu

|||

Why do you need to reduce the size of tempdb?

DO you have any free disk space issues?

I wouldn't suggest to shrink the TEMPDB and keep up the log size with regular transactionlog backups, as you have more information about Trans.log & Trans.replication relation.

Rick305652 wrote:

Ok Sounds logical, but when we have to reduce the size of tempdb, the way we do it is restart the sql server right ? In this case P2P configured has to go down. If yes, then what must be the ideal way of reducing the tempdb size on a system where P2P replication is configured ?

replication distribution server agents keep time out

Hi,
We have a distribution server that have all of our
transactional replications on it. All the distribution
agents and log reader agents keep time out and we have to
keep restart the agents.
What's the roob cause of this? Too many replications on
one distribution box? Or too many agents running at the
same time schedule?
Many thanks.
JJDon't the distribution agents keep trying for a certain number of times
before they error out? While they keep trying, you don't need to manually
restart them.
Distribution agents often times out in our environment. Causes I have found
are:
1. Blocking on the subscriber;
2. No more space on the subscriber database;
3. Server connection problem;
Richard
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:01a501c39284$479d5ee0$a401280a@.phx.gbl...
> Hi,
> We have a distribution server that have all of our
> transactional replications on it. All the distribution
> agents and log reader agents keep time out and we have to
> keep restart the agents.
> What's the roob cause of this? Too many replications on
> one distribution box? Or too many agents running at the
> same time schedule?
> Many thanks.
> JJ

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.

replication and log shrinking

Since I created a replication (snapshot basic - push) when I'm running the backup log ... with truncate_ only....the size of the transaction log don't lessens. why? (sql2005 without sp)I set to simple the recovery mode and back to full....nothing
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||

check of any open transaction . DBCC Opentran

Madhu

|||thnx
I run and and this is the result:

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4990:4944:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What can i do with this?|||

From BOL

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft? SQL Server?, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

you need to termintate this open transaction

http://msdn2.microsoft.com/en-us/library/ms182792.aspx

http://www.sql-server-performance.com/dbcc_commands.asp

google it you will fine many

Madhu

|||I stopped the replication (snapshot) and after I could shrink the log....
So this is a bug? ( the db running without any sp)

I tried....I re-set the replication and the symptom is occurred again....
so when I set a replication on this db, I can't shrink the transaction log anymore...

Replication and Log shipping work together

I want to setup replication and log shipping at
production environment, I have several questions:
1.What's the sequence of setup? replication first or log
shipping first or doesn't matter?
2.Does they work fine together?
Thanks
David Liu
moneris.com
MCSE MCDBA
1) replication first
2) log ship second.
Actually this is a complex question.
Here's what you need to do
1) backup your publication database, your distribution database, and your
msdb database
2) restore them on the standby server. Restore them with the
Keep_replication switch.
3) set the sync_with_backup option on your publication database, and
possibly your distribution database.
4) ship the logs.
5) when the publisher goes south, rename the standy server with the name of
the original publisher and reboot
6) user sp_replrestart and the skiperrors switch on the publisher until
everything works again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"David Liu" <david.z.liu@.moneris.com> wrote in message
news:5b8101c4813a$3f339d50$a601280a@.phx.gbl...
> I want to setup replication and log shipping at
> production environment, I have several questions:
> 1.What's the sequence of setup? replication first or log
> shipping first or doesn't matter?
> 2.Does they work fine together?
> Thanks
> David Liu
> moneris.com
> MCSE MCDBA

Replication and Log shipping

Sql server 2005 heavily hit database
Plans
1)Create a replicated databases to let users with query only access
2)Log shipping to allow a 4hour lag time with Disaster recovery just in
case
users gaff..
Your opinion
1)Transactional replication
2)Log shipping with a stagger of 4 hours
Not done this and wondering are there things to watch out for?
Are there better ways to go about this?
Is replication and log shipping in 2005 configured as a single setup?
Again your time and ideas are appreciated as alwaysI would create database snapshots on the local server if you have a large
tempdb and lots of memory. Otherwise I would use replication.
With log shipping your exposure to data loss if in excess of 4 hours. When
you apply the log you will have to kick your users off the system as you
apply the log to the database. With replication you can put user indexes on
the replicated subscriber database to offer better querying performance.
With log shipping the database is read only - you can't create indexes
there.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159285642.485892.323850@.b28g2000cwb.googlegroups.com...
> Sql server 2005 heavily hit database
> Plans
> 1)Create a replicated databases to let users with query only access
> 2)Log shipping to allow a 4hour lag time with Disaster recovery just in
> case
> users gaff..
> Your opinion
> 1)Transactional replication
> 2)Log shipping with a stagger of 4 hours
> Not done this and wondering are there things to watch out for?
> Are there better ways to go about this?
> Is replication and log shipping in 2005 configured as a single setup?
> Again your time and ideas are appreciated as always
>|||Massa,
I did an article a while back that will help you decide between the 2
options: http://www.replicationanswers.com/Standby.asp.
As you're using SQL Server 2005, I'd also add a couple more options for you
to consider looking at:
(a) Mirroring and database snapshots
(b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
variant on the standard Transactional Replication
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Folks for your time and great ideas ...............
Paul Ibison wrote:
> Massa,
> I did an article a while back that will help you decide between the 2
> options: http://www.replicationanswers.com/Standby.asp.
> As you're using SQL Server 2005, I'd also add a couple more options for you
> to consider looking at:
> (a) Mirroring and database snapshots
> (b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
> variant on the standard Transactional Replication
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Replication adn tempdb log file

I am relatively new to SQL Replication and am trying to configure replication
for some SQL databases for a disaster recovery project. I have several
databases ranging from 100MB to 5.5GB that I want to replicate to another
server offsite. I am starting with a small database. I go through the
Replication Wizard and it adds the articles. Then it sits on the create
publication screen forever and finally I get an error that says the tempdb
log file is full. I notice that the log file is over 10GB in size. Why is
that for a 100MB database? I have 17GB available on the server. How can I
create a publication for a database without filling up the tempdb log file?
If it uses over 10GB for a 100MB database what is going to happen when I
replicate a 5.5GB database?
Bill - such useage of tempdb is very odd - is it possible there is another
process occurring concurrently which fills the tempdb? Can you remove the
inactive entries to see if this helps:
backup log tempdb with no_log
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication & log shipping

Has anyone tried to do replication or log shipping on the same server? The
reason being I need a replica copy to server the reporting service so that
heavy loaded queries won't impact the online processing.
Thanks for any advice.
Joseph,
I regularly set up replication on the same server for testing purposes and
have not had any problems. If you haven't yet seen it, have a look at the
article here which describes the pros and cons of log shipping and
transactional replication for setting up a reporting server:
http://www.replicationanswers.com/Standby.asp
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication - log shipping

Hi,
how to tell which is better Replication or log shipping?, what are the advantages / disadvantages using each.
if I want to select one of these what all I need to consider?
Please reply.
RamRefer to books online for those 2 topics as a first hand information.

It depends on your environment, if you explain bit about it can give suggestion.