Home / server / Questions / 556004
Accepted
user49352
Asked: 2013-11-20 06:31:08 +0800 CST

SQL Server 2008R2 Transaction Replication Stop and Restart but continue log backups

  • 772

In a database that is currently being replicated I want to stop the replication (stop the log reader process) for a period of time though I want to continue to take log backups on that same database.

Does the log backup free up the log for reuse or does it remain intact for the log reader task to perform the replication to copy the transactions to the distribution database? All of this assumes that the space for the log is available for the log growth. I am thinking that whatever is remaining in the transaction log that is marked for replication that has not been distributed will not be overwritten but I wanted to verify this.

If so, when I restart the log reader process the transactions are written to the distribution database then the log would then continue to be handled as it was in the past and shrink appropriately.

sql-server-2008-r2

1 Answers

  1. Best Answer

    The log backup will not free up the log for reuse. Transactions in the transaction log in the publication database that have been marked for replication will not be unmarked until they have been written successfully to the distribution database. Only then can that portion of the log be marked for reuse.

    From the moment you stop the Log Reader Agent you will most likely notice some transaction log growth with log_reuse_wait_desc being REPLICATION.

    For example:

    SELECT name, log_reuse_wait_desc FROM sys.databases

    At first the log_reuse_wait_desc might be LOG_BACKUP, but after a log backup is performed, if the Log Reader Agent is not running, log_reuse_wait_desc will be REPLICATION.

    • 1