Subscription expiration solution: subscription

Subscription expiration solution: subscription

The Distributed Database executes a long transaction and reaches the 72 hours scheduled by the system.Subscription expiredThe database distribution agent is no longer available. The error message is as follows:

Error message: the subscription has been marked as inactive and must be reinitialized. You need to delete NoSync subscriptions and recreate them.

Right-click to subscribe and find that the subscribe is already inactive !~

How can this problem be solved? Do you want to reinitialize it ??!

Later, we found a system stored procedure sp_changesubstatus, which can change three subscribed states: active, inactive, and subscribed.

-- This stored procedure executes exec sp_changesubstatus @ publication = 'your publication name' in the Publishing Database of the Publishing Server, @ subscriber = 'your subscriber name', @ status = 'active'

After the task is executed, close and start the distribution agent job again !~ The prompt is still the same !~ Later, we found that if the subscription expires, the stored procedure could not be changed and the change was invalid !~

You can change the status of a subscribed activity, that is, to change the status field of a table MSsubscriptions in the distribution Library (0 = not active; 1 = subscribed; 2 = active ), 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 the task is executed, close and start the distribution agent job again. Soon, the replication is normal !~

Test the operation data in the publishing and subscription databases. The synchronization is normal! It's always done !~

In addition, because the maximum transaction life period by default is 72 hours, if a transaction is not delivered to the subscription during this period"Clear distribution"When the job is marked as disabled, the subscribed transactions are cleared. If the subscription expires more than 336 hours (14 days), the subscription will be"Expired subscription cleanup"Delete a job execution. (For more information, see subscription expiration and deactivation)

To avoid problems caused by expiration, you can set two methods:

1. Disable the cleanup tasks;

2. Set a longer shelf life.

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

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.