SQL Server Subscription Expiration workaround

Original: SQL Server subscription expiration workaround

The database Distribution Agent is no longer enabled because the distribution database is performing a long transaction that reaches the system's scheduled 72 hours, causing the subscription to expire and the following error is indicated:

Error message: This (these) subscriptions have been marked as inactive and must be reinitialized. You need to delete NoSync subscriptions and recreate them

Right-click on the subscription and find that the subscription is already in a live state! ~

How to solve? Do you want to reinitialize??!

Later found a system stored procedure sp_changesubstatus, which can change the 3 states of the subscription: active, inactive, subscribed.

--This stored procedure executes exec sp_changesubstatus in the publisher's publication database  @publication = ' Your publication name ', @subscriber = ' Your Subscriber name ', @status = ' active '

After execution, the Distribution Agent job is re-closed and started, and found to be of no effect! ~ The hint is still the same! ~ later found that if the subscription expires, the stored procedure cannot be changed, the change is invalid! ~

With regard to the activity status of the subscription, there is one other place that can be changed, that is, changing the status field of a table mssubscriptions in the distribution library (0 = inactive; 1 = subscribed; 2 = active), and each published item in the subscription occupies one row in the MSsubscriptions table.

--select * from Distribution.dbo.mspublications--select * from Distribution.dbo.MSsubscriptions where status<> 2update Sub Set status = 2from distribution.dbo.MSsubscriptions sub where status<>2 and publication_id=27

After execution, the Distribution Agent job is re-closed and started, and soon, replication is OK! ~

Test the operation data in the Publish and subscribe library, Sync Normal! It's always done! ~

Note: Updates at the subscriber will still propagate to the publisher even if the subscription expires or is inactive.

Also, because the default maximum transaction shelf life for the system is 72 hours, if a transaction is not delivered to the subscription during this period, the purge distribution job is marked as deactivated and the transaction for that subscription is emptied. If the subscription expires more than 336 hours (14 days), the subscription will be deleted by the expired subscription cleanup job. (More reference subscriptions expire and deactivate)

To avoid problems caused by expiration, there are two ways to set it up:

1. Disable the "expired subscription cleanup" job;

2. Set a longer shelf life.

This article comes from " HELLO.KK (SQL Server)"blog, reproduced please be sure to keep this source http://blog.csdn.net/kk185800961/article/details/46688827

SQL Server Subscription Expiration workaround