Error in replication: the subscription(s) have been marked inactive and must be reinitialized

There are several reasons for this error to appear, mostly they are network connection problems, but also downtime of the subscriber beyond the subscription retention period or simple schema changes.
So the first thing to do is to investigate the reason for the issue. As always, Google is your friend and you’ll find some great sites helping you in this investigation.

Relevant data for this investigation can be found in

select * from distribution..MSdistribution_agents
select * from distribution..MSdistribution_history order by time desc
select top(50) * from distribution..MSrepl_errors order by time desc
	
exec sp_browsereplcommands
	
select * from distribution..MSrepl_commands

Reinitializing the replication can be a pain, especially with large data volumes. In some cases reinitializing can be avoided. You can re-activate the subscriptions, but you’ll have to do so directly in the distribution database.

First find the subsriptions that need to be activated.

select * from distribution..Mssubscriptions

Take a look at the status column:

  • 0 = Inactive
    1 = subscribed
    2 = Active
  • Then you can reactivate them by resetting the status to Active:

    update distribution..Mssubscriptions set status = 2 where status = 0 and publisher_db = '<YourDB>'

    After reactivation check the Replication Monitor and the above tables how your replication is doing. There might be several errors you need to solve, but generally this should help get the replication going again.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    %d bloggers like this: