“The Only Thing That Is Constant Is Change”

Logic for Expiring Subscriptions in Transactional Replication.

Posted by database-wiki on April 11, 2016

A subscription is marked as INACTIVE or EXPIRED when it has not synchronized successfully within the TRANSACTION RETENTION period. In our case, this period is 36 hours. This means that if a subscriber has not received transactions for a 36 hour period, it will be marked as INACTIVE by the “Distribution clean up” SQL Agent job. The logic that goes into marking a subscription as inactive evaluates the last distributed transaction for the given distribution agent. It gets this information from the distribution_history table in the distribution database. It then finds the next greatest transaction, and if that transaction has an entry_time older than the TRANSACTION RETENTION period, the subscription is marked inactive.

You can check this logic at any time to determine if a subscription is going to expire. We did this on the phone using the actual text from a replication stored procedure called sp_MSsubscription_cleanup. Here is an example on how you might check:

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

left join (MSdistribution_history h with (REPEATABLEREAD)

join (select agent_id,

max(timestamp) as timestamp

from MSdistribution_history with (REPEATABLEREAD)

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 = 2

and s.subscriber_id >= 0

group by s.agent_id,


isnull(h.xact_seqno, 0x0)

This query returns 4 columns and a row for each distribution agent. You can find the agent names in the MSDistribution_Agents table. 

Copy the value from the xact_seqno column as this is the last transaction that has been distributed. Use it in the following query as a static value for the variable @DerivedXactSeqno:

declare @DerivedXactSeqno varbinary(100) = <xact_seqno>

declare @DerivedPubDBID int = 24

declare @DerivedAgentID int = 97

declare @max_time datetime

select @max_time = dateadd(hour, 6, getdate())

select top 1 entry_time, *

from MSrepl_transactions t,

MSrepl_commands c,

MSsubscriptions sss

where sss.agent_id = @DerivedAgentID

and t.publisher_database_id = @DerivedPubDBID

and c.publisher_database_id = @DerivedPubDBID

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 & 0x80000000) <> 0x80000000

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( @derivedxactseqno, 0x0 )

and c.xact_seqno > isnull( @derivedxactseqno, 0x0 )

order by t.xact_seqno asc–), @max_time)

–select @max_time

select dateadd(hour, 36, getdate())

If the Entry_Time returned by the query is more recent than the retention period then the subscription will NOT be marked INACTIVE the next time that the “Distribution clean up” job runs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: