How do I go about pausing transactional replication on the subscriber end.

We have a publisher on Server A, a distributor on Server B and a subscriber on Server C. All are in the same network.

Some background, we have quite a busy BizTalk server where we replicate the data from the BAM parts to a separate database server which will be used for a customized web application for statistics, searching etc. Seeing as the tables in the BizTalk server are both quite large and missing indexes etc since they're basically just there to have data dumped into them we opted to have the second, replicated database in which we can apply various optimizations for reading the data.

One thing I've noticed though is that adding a basic, non-clustered, index takes forever and causes all kinds of locks (we're using SQL Server Standard so Online indexing isn't an option unfortunately) I was thinking that giving the database a bit of respite from all the write operations from the BizTalk data while creating the indexes would be a good idea (right or wrong?).

Question is, how do I pause the replication (without losing any data from the publisher) on the subscriber end while creating the indexes?


Replication runs using "Agents"--essentially external executables that do the work. For transactional Replication, you have three agents:

  • Snapshot Agent - this is the process who does the snapshot to initialize Replication (assuming you aren't initializing via another method. Most people use snapshots). There is one per publication. It lives on either the Publisher or the Distributor.
  • Log Reader Agent - this is the process that reads the transaction log on the publisher, identifies changes, and writes those changes to the distribution database on your distributor. There is one per publication. It lives on either the Publisher or the Distributor.
  • Distribution Agent - this is the process that takes changes from your distribution database and pushes it to the subscriber. There is one per subscriber. It lives on either the Subscriber or the Distributor.

The standard setup is that the agents run as SQL Agent jobs. If you want to stop distributing commands to a subscriber, simply stop the jobs. You can find distribution agents based on the job category of REPL-Distribution. The job name will be composed to include publisher/subscriber info to help you identify the correct job to disable. Those values may be truncated when making the job name, so if you have multiple distributors, you may need to look at the distribution job step itself to be 100% sure you have identified the correct job.

Note you can also confirm the correct job by querying the distribution database--but I generally find it quicker and easier to look at the job itself.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.