Take the 2-minute tour ×
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 stackoverflow.com 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 blogs.technet.com/b/claudia_silva/archive/2009/05/04/…. 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 days...it 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

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

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: msdn.microsoft.com 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

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
share|improve this answer

Your Answer

 
discard

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.