Pages

Wednesday, April 29, 2015

Find subscriptions soon to be deactivated

By default, a transaction that has been delivered to the subscriber is deleted from the distribution database by the Distribution clean job that runs every 10 minutes. If for some reason the transaction cannot be delivered it will be deleted after the max retention of 72 hrs, the subscription will be marked inactive, and we will get this error message:

"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