locked
The subscription(s) have been marked inactive and must be reinitialized RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    I keep getting the following error on Transactional Replication setup in SQL 2005.

    Error messages:

    The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074)
    Get help: http://help/21074


    The subscription expiration option has been set to
    "Subscriptions never expire, but they can be deactivated until they are reinitialized."

    I know we should not update the system tables but updating distribution..MSSubscriptions status to 2 resolves the issue.

    Is there a fix for this problem?


    Thanks

    Monday, December 15, 2008 6:55 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    There is a known bug where subscriptions will be marked inactive if the immediate_sync publication property is set and a replication monitor proc is executed (which will be the case if you use monitor)
    The latest CU has the fix
    • Marked as answer by gopal ashok Thursday, December 18, 2008 10:59 PM
    Thursday, December 18, 2008 10:59 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    The best way around this is to set history retention and your publication retention settings to values which exceed the maximum length of time your subscriber could be offline for and then be very proactive to ensure your subscribers are always syncing.



    Hilary
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, December 15, 2008 8:45 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote
    The retention period is set to more than a week, i doubt the subscriber would be inactive for more than a week in my environment.
    Tuesday, December 16, 2008 1:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, I agree with Hillary, the best practise is to check retention, but if are sure the you subscription has been sync, so, you can run an update on "syssubscriptions" table to turn your subscription "activated" again.
    Set column status to 2 to this subscription to turn it alive.

    Regards...Emanuel Peixoto. Press Yes if the post is useful.
    Tuesday, December 16, 2008 1:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    There is a known bug where subscriptions will be marked inactive if the immediate_sync publication property is set and a replication monitor proc is executed (which will be the case if you use monitor)
    The latest CU has the fix
    • Marked as answer by gopal ashok Thursday, December 18, 2008 10:59 PM
    Thursday, December 18, 2008 10:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hilary...you mean changing transaction retention and history retention in distributor properties?

    Monday, May 18, 2009 10:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    yes!
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, May 18, 2009 11:00 PM
    Answerer
  • Question
    Sign in to vote
    3
    Sign in to vote

    I'm getting the same error for my sql server 8.0.2282. It has got all the CU packages updated.

    Pls help if anything can be done to avoid in future... Or any workaround to fix them without reintiating the subscriber..??

    - Kerobin
    Monday, October 18, 2010 5:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hilary,

    I too got the same error. I have changed the settings(Increased the time )and restarted the services.

    But, Still getting the same error. 

    Any help please ?

    Tuesday, September 4, 2012 2:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can always SQL,

    follow the link below you will get the some thing like this, so before sync you have upload the unsync data and for that you have to change the properties in system table, and after that take the full backup of subscription DB for later check. then reinitialize the subscriber DB as new instance from PUB.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/e6124f37-1a3a-4cda-9584-3ebcb229b8c6/

    if you have any issue at any stage you can restore the backup DB for future reference.

    Regards JakDBA Please Mark this as Answered if this solves your issue or satisfied with the response.

    • Proposed as answer by Tebele Thursday, July 9, 2015 9:04 AM
    Saturday, September 8, 2012 6:06 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    you can try the following steps :

    Try to tun following script :

    At publisher:
    use distribution
    go
    update MSSubscriptions set [status] = 2
    To review pending commands to replicate. At plusblisher:
    use distribution
    go

    exec sp_browsereplcmds

    it helped me..

    Thursday, December 4, 2014 7:17 AM