Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from Jan 30 '13 at 2:10

This question came from our site for professional and enthusiast programmers.

I've never had to re-init a subscription where there were connection issues that were resolved within the distribution database's minimum retention period. What is that setting for you? –  Ben Thul Jan 29 '13 at 19:22
we have retention set to 0 (infinite) –  JeffN825 Jan 30 '13 at 18:01
I don't think 0 means infinite here. Check out…. Either way, you're not storing them for an infinite amount of time. How big would your distribution database be if you were? –  Ben Thul Jan 30 '13 at 20:42
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 seems sporadic. Is there any way to tell WHY SQL decided in its wisdom to mark them inactive? –  JeffN825 Jan 31 '13 at 6:17
I'd try setting your min_distretention higher than 0. You didn't mention what your max was, but I suspect that the cleanup agent is cleaning up commands before they have a chance to get replicated to your disconnected subscriber. I typically set it to a day (i.e. 24, since min_distretention is in hours). –  Ben Thul Jan 31 '13 at 16:09

2 Answers 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
-- 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)
UPDATE distribution..MSsubscriptions
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 ---'
print 'The subscription is not INACTIVE ... you are good for now .... !!'
share|improve this answer

According to

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,



Also please read here about solutions:

share|improve this answer
We have retention set to 0 (supposedly infinite) –  JeffN825 Jan 29 '13 at 23:50
Any other ideas on why it would mark them as inactive? –  JeffN825 Jan 30 '13 at 0:29
please look here: It is possible to specify that subscriptions never expire (a value of 0 for @retention), but it is strongly recommended that you do not use this value, because metadata cannot be cleaned up. –  Dalex Jan 30 '13 at 19:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.