Assistance Required on Transaction Log Replication

Question

Hi,

Summary

How can I gracefully stop replication so that the lock on the live database transaction log file can be released allowing the service broker queue to catch up which updates the live database?

Detail

We have a database running on SQL Server 2008 Standard Edition which is Replicated on another server which is also running SQL Server
2008 Standard Edition.

We use transactional replication which is set to run in continuous mode.

Frequently Asked Questions Reference; - http://msdn.microsoft.com/en-us/library/ms151740.aspx

“Does replication affect the size of the transaction log?

Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running. For more information on checking Log Reader Agent
status, see How to: View Information and Perform Tasks for the Agents Associated With a Publication (Replication Monitor).”

Basically the transaction log has grown too large and consequently the live database has not been updated since Sunday 16:45.

The replicated database has not updated since Friday 2nd March.

The service broker queue whose job it is to update the live database is growing in size without the live database being updated because replication has a lock on the transaction log file of the live server.

So how can I gracefully stop replication so that the lock on the live database transaction log file can be released allowing the service broker queue to catch up?

Request for Advice / Clarification

As anyone else been in this situation before if so please share with me your resolution.

Thanks in advance,

Kieran.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood

Answer 1

Transactional Replication uses a Log Reader to read the transaction log on the Publisher.  If you open the SQL Server agent on the distributor (could be same server but could be stand alone as well) and find the job with a category of REPL-LogReader that would be your log reader agent.  If you stop that job that will stop the log reader and should free up the resources in the transaction log.  You could also modify the Log Reader agent profile to a longer polling interval which would be a second option.  Also rememebr to start back that log reader job after service broker has gotten caught up

Answer 2

Can you paste more blocking information? 

Your service broker does update to live database? This will just do the update and write to log.

Transactional replication will just simply read the log file and put across to replicated database. I can't see why they are blocking each other.

---

If you think my suggestion is useful, please rate it as helpful.
If it has helped you to resolve the problem, please Mark it as Answer.
http://twitter.com/7Kn1ghts

Answer 3

A suggested resolution we have come up with so far; -

Depending on estimated time to read pending transactions and transfer them to the subscribers, it may be faster to mark all transactions as “replicated”, then reinitialize the subscribers with a new snapshot or via backup/restore.  This step should only be taken if the time to generated a new Replication Shapshot and deliver to subscriber is faster than waiting for individual pending commands to be replicated.
http://msdn.microsoft.com/en-us/library/ms173775.aspx

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1

Any thoughts on this suggested resolution please?

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood