Re: effect Replication has on transaction log

Tech-Archive recommends: Fix windows errors by optimizing your registry

Transactional replication has effects on the transaction log. Merge and
snapshot don't.

With transactional replication the vlf's will have their status set to 0 if
there are no active transactions recorded there, and there are no committed
transactions in there which have not been marked for replication. Otherwise
a backup of the transaction log with full recovery model will not affect the
status column and the vlf cannot be used again, or the transaction log
cannot be shrunk before this boundary.

So with merge, put your db in bulk load recovery model while the snapshot is
being applied, full after and dump at a frequency which maintains the size
of the log. With SQL 2005 log autogrows aren't the performance killer that
they were with SQL 2000.

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



"Will" <william_pegg@xxxxxxxxxxx> wrote in message
news:1145888281.366687.225950@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi All,

Apologies if this seems silly, only I'm not so good on the transaction
log side of SQL.

I've got a subscriber set up with merge replication to a publisher (SQL
2000). When the database was set it up defaulted to full recovery model
(I hope to change this soon).

As far as I know, full recovery model means that transactions in the
transaction log are not marked as for deletion until the database is
backed up. Given it's just a subscriber there is no backup gob ever
being performed.

So I'm worrying that there's a subscriber out there with the
transaction log just growing and growing. However I would have thought
that this situation would be expected by SQL server, so I was wondering
if replication caused a similar effect on the subscribers transaction
log that a backup would?

Thanks
Will



.

Relevant Pages

  • Re: Distribution Agent Startup Parameters
    ... have a manageable log size when a concurrent snapshot is applied in ... transaction log for large tables? ... the SQL2000 index creation logic in the distribution agent is not very ... having problems with the transaction log growing so large on the ...
    (microsoft.public.sqlserver.replication)
  • Inactive Transaction/Log will not truncate after replication disabled
    ... I set up a transactional replication from 'DB1' at node A ( ... scheduled transaction backup run. ... I set up the push replication at 'DB1' with published article 'T' and not ... noticed that the Transaction Log in the 'DB1' keep growing even after log ...
    (microsoft.public.sqlserver.replication)
  • Re: Distribution Agent Startup Parameters
    ... transaction bounding the delivery of a concurrent snapshot in SQL2000, ... SQL2000 index creation logic in the distribution agent is not very ... having problems with the transaction log growing so large on the ...
    (microsoft.public.sqlserver.replication)
  • Concurrent access for snapshot
    ... Transactional replication has the posibility of reading ... the transaction log, hence the ability to do concurrent ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • RE: Exchange Logs SBS 2k
    ... I will explain the transaction log of exchange. ... perform a full online backup to free up your disk space. ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)