Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm playing around with SQL Server Agent alerts, hoping that I can get some reliable error notifications sent out when our transactional replication breaks. To test it however, I need to break it. I have a subscription that's safe to break, but I don't want to have to spend hours reinitializing and delivering snapshots afterward. Is there an easy, reversible way to break replication?

share|improve this question
up vote 3 down vote accepted


Since the SQL agent jobs that perform replication cannot connect to a database in single-user mode, this has the simultaneous result of stopping replication, and disallowing client connections.

Ergo, replication is broken but it can be resumed without re-initializing (the LSN is still good)

share|improve this answer

What about testing it to perfection in a VM environment and then duplicating it to the real servers. A VM can easily be rolled back to a previous state so you can always start over again.

share|improve this answer
Effort. In our case it's fine to break replication in production for a minute or two, and far less of a cost than duplicating our environment. I sure wish we had replication in our test environment, though... – Stuart Branham Jul 6 '12 at 20:00

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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