Stopping SQL Server Log Reader Agent In 2012

Question

We implement Transactional Replication with 2 publishing servers, 4 publications and 1 subscribing server. I need to shut down our distribution server for several hours. When restarted, will the Log Reader be able to pick back up where it stopped reading the logs before the shutdown?

My thought is that, because transaction log backups will continue on the publishers and clear the logs, the log reader will not have anything to grab.

I believe that my only reliable option is going to be reinitializing each publication and starting over.

Is my understanding of this correct or am I over complicating things?

Thanks!

Answer 1

SQL won't clear transactions from the log that the log reader hasn't marked as done (Committed to Distribution). So your log may grow while the Distribution server is down, but once it comes back, the log reader should be able to catch up over time.

From BOL:

The Log Reader Agent runs at the Distributor; it typically runs continuously, but can also run according to a schedule you establish. When executing, the Log Reader Agent first reads the publication transaction log (the same database log used for transaction tracking and recovery during regular SQL Server Database Engine operations) and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data in transactions that have been marked for replication. Next, the agent copies those transactions in batches to the distribution database at the Distributor. The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.

After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed. Finally, the agent marks the rows in the transaction log that are ready to be purged. Rows still waiting to be replicated are not purged.

Answer 2

for several hours you will be fine, one of the conditions for a log reuse is whether or not all of the records in the VLF have been processed by the log reader.

Keep the log reader running, find out and stop the distributor agent job for each of the publications affected by the distributor server going down, before you switch the distributor server off.

I have posted the scripts for this on the link below:

subscriber table needs to be updated - how to use a batch process AND how not to upset the replication while doing it?