SQL Server Performance Forum – Threads Archive

SQL Server Replication Log Reader T.Log?

1) I need confirmation on whether it is possible to truncate a log of a database which is being replicated to a destination server.
2) In other words the log reader job will be running on the transaction log but cannot allow the transaction log to be truncated as the log reader needs to maintain consistent pointers/place markers in the log – is this correct?
3) Is there a way round this but still maintain the log reader and replication order?
4) What happens if you stop the data entry, stop the log reader and truncate the log, then restart the log reader – will the correct data be replicated next time?
Thanks…

You can truncate a log that is involve in replication. The active transaction will not be truncated. I based on BOL and experience. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of the following events: -The most recent checkpoint. -The start of the oldest active transaction, which is a transaction that has not yet been committed or rolled back. This represents the earliest point to which SQL Server would have to roll back transactions during recovery. -The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet. This represents the earliest point that SQL Server still has to replicate. <hr height="1" noshade id="quote"></blockquote id="quote">

Why you want to manipulate transaction log that is involved in transactions, the best point is to maintain regular Tlog backups or let us know what is the existing problem in the replication. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for your replies. I want to manage the size of the transaction logs.
5) I don’t think you can shrink the transaction log whilst the log reader is running can someone confirm this?
6) Does the log reader read the transaction log inactive log entries only. And does it only then submit the inactive transactions via replication? Thanks…

Yes the log will not be SHRUNK if you attempt while a transaction is in OPEN state.
SQL Server will execute one Log Reader Agent job at a time in a cycle that encompasses all the replicated databases. Thus, you change many jobs executing in parallel into a series of jobs that cycle through the databases. Besides decreasing the processor load, another less obvious advantage of using scheduled Log Reader Agents is that now you can access the Log Reader Agent history and see, for example, the time of day when the maximum number of transactions occurred. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya – however I want to clarify question 6) – How is it possible to shrink the transaction log if the log reader is running? It looks like the log reader will fail if a shrink log is attempted – is this correct? Also, If we stop data entry, ensure the last transaction is sent from the log reader to the subscriber via transactional replication, then stop the log reader, then shrink the log – does this mean the log reader only sends the inactive part of the log? – and if we then restart the log reader and restart data entry then will the log reader be able to send the new transactions via replication or is the sequence in the log reader destroyed???? Hope this is clear this time…

]]>