SQL Server Performance Forum – Threads Archive

Recovery model and transactional replication

Today I was wondering this simple thing. If I have a database in recovery model simple, how the transactional replication works ? If every time I commit a transaction and because the recovery model is simple, SQL is supposed to "discard" that part of transaction log, how is the log reader agent supposed to read that part of the log ? I’m suspecting that the transactions maked for replication are not actually discarded from transaction log until the log reader reads and ship them to the oter server.
and somehow the log reader agent mark them as "ready". Is that so ?
How is that really working ? Thanx.

They both use the transaction log, but they don’t interfere with each other. The transactions won’t be removed from the tran log until after replication is done with them. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

OK. this is more or less what I have thought. And suppose the replication don’t take place for a long time. (for whatever reason) that way the transaction log can grow without bounds, even if its DB is set in recovery model simple ?
right ? cause if the transactions maked as for replication are not cleared or something by the agent log reader the will stay there. right ?

No if the transactions that are not marked for replication will be truncated as soon as the checkpoint reaches 80% of transaction log. Only in the case of optimization or any bulk load jobs the Tlog needed to be sized to higher size in order to accomodate the process in that operation. If the transactions are not committed then it will be left on Tlog and will not be truncated.
Please read Transaction log architecture, simple recovery model and checkpoint topics in BOL. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I’m talking (hypotetically) that there are a lot of transaction MARKED FOR REPLICATION, and that the agent log reader is not running. I don’t think a commited transaction maked for replication will be removed from transaction log no matter what size it is (until log reader runs of course).
I suppose the transaction log will autogrow (if it is set to do so of course) This is my "grow without bound" scenario. Am I understanding something wrong ?

Absolutely, your understanding on the 4th post is correct. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Then problably I have explained myself wrong in the first three [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]<br />

]]>