Hugo Kornelis (1/23/2014)
L' Eomot Inversé (1/23/2014)
Interesting question. I thought it worked for all models, but ir's so long since I last looked at replication that I thought I might be wrong, so checked in MSDN. The page I checked was The Transaction Log, which seems to say that log records are retained until the data has been replicated only in full recovery model, which seems to me to imply that transactional replication will not work in either simple or bulk-logged model, so (thinking that MSDN was more likely to have that right than my memory) I picked that answer - the wrongest one of the three.Is that page wrong, or do the other recovery models have some method of delaying the discard of log records pending replication other than by setting log_reuse_wait to 6 in the affected records? Or perhaps in this question "replication works" means "snapshot replication works" (snapshot uses value 7, not value 6, to delay truncation)?
When replication is used, the log is not reused even when you are in simple recovery. Once replication has catched up, it will be available for reuse. (In full recovery, log records will not be reused until both the log backup and replication have catched up).
Yes, but how is release of the log records prevented? If the log_reuse_wait attribute in sys.databases is not set to 6 and there is no other value that indicates that transactional replication requires the log not to be truncated (value 7 is explicitly for snapshot creation, so not relevant to transactional replication except possibly while an initial snapshot is being created, and nothing else seems remotely relevant) it looks as if some method is used that doesn't use that attribute, so it seems that it can't be this attribute being nonzero that tells the system that it can't recover any virtual log files. Maybe simple and bulk logged recovery models set it to 1 when no VLF can be retrieved because of transactional replication?
Tom