SQL Server replication subscriptions marked as inactive

Question

Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever?

It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription every time.

Note, I'm not talking about the subscriptions being marked as expired...just as inactive.

Thank you.

Answer 1

According to http://blogs.msdn.com:

In order to find the cause of deactivation we need to check the Transaction Retention period in the Distribution properties. If it is set to a lower number like 3 hours, then this indicates that if the subscriber does not synchronies in 3 hours, the distribution cleanup agent will truncate the commands tables in distribution and hence the subscriber will be marked as deactivated.

Since the commands are deleted from distributor there is no other option but to reinitialize the subscriber.

Also to find the cause of the Distributor not being able to sync we can check the Distribution history in the below tables on distribution database,

msdistribution_agents

msdistribution_history

Also please read here about solutions:http://blogs.msdn.com

Answer 2

Yes you can do that programatically using t-SQL and a sql agent job.

Basically, you have to query distribution..MSSubscriptions and check the status column value to be 0 (Inactive).

Status of the subscription: 0 = Inactive; 1 = Subscribed; 2 = Active

Below T-SQL Will help you :

-- Author: Kin Shah
-- Date: 4-1-2013
-- For dba.stackexchange.com
-- Good to find out 
-- publisher_id
-- publisher_db
-- publication_id
-- subscriber_id
-- subscriber_db
select * From distribution..MSsubscriptions

--- based on the above values, run below statement
--- this can be run using SQLAgent job

if exists (select 1 from distribution..MSsubscriptions where status = 0)
begin
UPDATE distribution..MSsubscriptions
SET STATUS = 2
WHERE publisher_id = '--publisher_id -- will be integer --' 
    AND publisher_db = '--publisher db name ---'
    AND publication_id = '--publication_id -- will be integer --'
    AND subscriber_id = '--subscriber_id -- will be integer ---'
    AND subscriber_db = '-- subscriber_db ---'
end
else
begin
print 'The subscription is not INACTIVE ... you are good for now .... !!'
end