Transaction Replication : subscriptions get invalidated all the time

• timber

  Old Hand

  Points: 336

  More actions

  March 9, 2022 at 7:33 pm

  #3996425

  Dears ;

  Layout:

  SQL 2019 publisher/distributor  server (standard) with push subscribers (various versions, express)

  Problem :

  Multiple subscribers throw this alert every day in the publisher/distributor SQL errorlog:

  The subscription(s) have been marked inactive and must be reinitialized. Nosync subscriptions will need to be dropped and recreated.

  Settings :

  Publication retention : 1440 hours

  Distribution transaction retention : 0-744hours / history retention 168hours

  Conclusion :

  So I'd expect that this error would only happen if a subscriber is not syncing up within 744 hours (31 days). But there is something else going on it seems... far to many subscribers throw this error. Any ideas?

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  March 10, 2022 at 8:10 pm

  #3997153

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  March 11, 2022 at 1:57 pm

  #3997515

  In Publication properties, it is best to mark it as "Subscriptions never expire".

  Also you could execute the below to bring it back to normal state.

  USE distribution

  GO

  Update [MSsubscriptions]

  Set [status] = 2

  where publisher_db = 'YourDBName'

   

  -Roy