Trans Repl & Pub Backup Strategy

Publisher (Distribution db on remote db, & Subsciber)
- recovery model = FULL
- Last database backup: None
- Last transaction log backup: None


We are trying to evaluate the most effective Backup/Restore strategy for our
publisher. In Books OnLine we discovered the caveat w/ the Log Reader Agent
truncating the tran log, and the syncwithbackup option. In order to test, we
were first going to run syncwithbackup = FALSE, then changing to TRUE. The
FALSE setting was tested first and gave results completely different from
documentation and expectations.

Test1: syncwithbackup = FALSE
500K update transactions executed against pub. What we would expect was to
see the transaction log at the publisher truncating each time the Log REader
Agent fires, what we saw was an ever growing tran log at the publisher, no
truncate occurred. Tran log grew from 2 Mb to 1,000 Mb.

At the subscriber, the transaction log remained relatively static, it
appeared to fluctuate a couple of meg up and down in size, however stayed
relatively the same 2 Mb size throughout the initial test.

Our Strategy:
We were hoping, in the event of catastrophic failure at the publisher, and
not to introduce latency nor run tran log backups every 2 minutes, that we
could use a full backup at the subscriber and the tran log backups at the
subscriber to restore the publisher to a point in time. Unfortunately the
subscriber's tran log doesn't appear to record the transactions coming from
the distributor, or they are getting truncated immediately...

Questions: Why is my tran log not truncating at the publisher when
synchwithbackup = FALSE? Why is the tran log at the subscriber not affected,
or affected but immediately truncated. All databases are set to FULL recovery
model.

Thanks in advance,
Chris

Chris, DBCC OPENTRAN will report if you have an open
transaction which prevents the log from being truncated.
In your case I would expect this is the case. I agree
that if the log reader has read the transactions then
backing up the log should ordinarily truncate it.

For the other issue, if the subscriber is set to full
recovery mode, then there's something strange if the
subscriber's log is not increasing as the transactions
are being applied. This sounds more like Simple mode.
Have you used LogExplorer/LogPI to see what is happening?

Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)