"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."
Once it is deactivated only reinitializing it will activate it back because data will be missing so it is the only way to assure consistency.
To avoid the deactivation, we should find why the transaction cannot be delivered and fix it before the max retention. However, depending on how the subscriptions are being monitored, an error may not surface until it is too late, for example if the max number of retries of the distribution agent is set to the default, replication monitor won’t show an error until 2,147,483,647 retries.
To find the subscriptions soon to be deactivated, we can use the same logic the stored procedure sp_MSsubscription_cleanup uses, but instead of using the max retention period of the distribution database, we can use a custom value on the variable @cutoff_time.
use distribution
declare
@ACTIVE tinyint
,@INACTIVE tinyint
,@SUBSCRIBED tinyint
,@VIRTUAL smallint
,@SNAPSHOT_BIT int
,@max_time datetime
,@cutoff_time datetime
,@max_distret int
select
@ACTIVE = 2
,@INACTIVE = 0
,@SUBSCRIBED = 1
,@VIRTUAL = -1
,@SNAPSHOT_BIT = 0x80000000
-- set @cutoff_time to how old the transaction need to be in the queue to show results
select @cutoff_time = dateadd(hour, -1, getdate())
select @max_time = dateadd(hour, 1, getdate())
-- get the distribution agent with the oldest record in the queue
select derivedInfo.agent_id, derivedInfo.xact_seqno, derivedInfo.publisher_database_id
into #temp
-- exact code from sp_MSsubscription_cleanup
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select s.agent_id as agent_id,
s.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
isnull(h.xact_seqno, 0x0) as xact_seqno
from MSsubscriptions s with (nolock)
left join (MSdistribution_history h with (nolock)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (nolock)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on s.agent_id = h.agent_id
where s.status = @ACTIVE
and s.subscriber_id >= 0 -- Only well-known agent
group by s.agent_id, -- agent and pubdbid as a pair can never be differnt
s.publisher_database_id,
isnull(h.xact_seqno, 0x0) -- because of join above we can include this
) derivedInfo
where @cutoff_time >= (
-- get the entry_time of the first transaction that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno
then
-- join with commands table to filter out transactions that do not have commands
isnull((select top 1 entry_time
from MSrepl_transactions t with (nolock),
MSrepl_commands c with (nolock),
MSsubscriptions sss with (nolock)
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
and c.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t with (nolock),
MSrepl_commands c with (nolock),
MSsubscriptions sss with (nolock)
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= derivedInfo.subscription_seqno
and c.xact_seqno >= derivedInfo.subscription_seqno
order by t.xact_seqno asc), @max_time)
end)
-- get max distribution retention
select @max_distret = d.max_distretention
from msdb.dbo.MSdistributiondbs d with (nolock)
where d.name = db_name()
-- get subscription name of oldest record in the queue soon to be deactivated
select
ps.name publisher
,a.publisher_db
,a.publication
,ss.name subscriber
,'Last replicated transaction time: '
+cast(r.entry_time as varchar(25))
+'. Hours left before deactivation : '
+cast(@max_distret - datediff(hour, r.entry_time, getdate()) as varchar(10)) status_message
from #temp t
join MSrepl_transactions r with (nolock)
on t.xact_seqno = r.xact_seqno
and t.publisher_database_id = r.publisher_database_id
join MSdistribution_agents a
on t.agent_id = a.id
join sys.servers ps
on a.publisher_id = ps.server_id
join sys.servers ss
on a.subscriber_id = ss.server_id
drop table #temp