Sql-server – SQL Server replication subscriptions marked as inactive

replicationsql serversql-server-2005

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.

We have retention set to 0 (infinite). I'd rather have a big distribution database than have to reinitialize a 15GB database to a subscriber over the WAN. Anyway, the real issue here is that they are getting marked as inactive within just a few hours sometimes…or sometimes a few days…it seems sporadic. Is there any way to tell WHY SQL decided in its wisdom to mark them inactive?

Best Answer

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
Related Question