Microsoft SQL Server Code Name "Denali" CTP1 is available for download now.
You can download it from Microsoft SQL Server Future Editions A complete set of enterprise-ready technologies and tools
Sunday, November 14, 2010
Wednesday, November 10, 2010
Expired Subscriptions in SQL Server
Hi all,
Currently I am working on a huge setup of servers which utilizes replication quite extensively. Each of these servers has about 4 to 10 publication and around 10 to 40 Subscribers running 24x7. An issue, I was facing quite regularly on many subscribers was that they tend to expire and then the second step of the replication job simply goes in the retry mode.
I investigated the issue by first finding the replication and then going into one of the tables in 'Distribution' database of my distributor. The table is dbo.MSsubscriptions. It is a very usefull table with the complete info about all the Subscriptions configured on this distributor. It contains the following columns of our interest -
publisher_database_id, publisher_db, subscriber_db and status
if you look for the value of the status column corresponding to the right Publisher and Subscriber Database, it would be either 0, 1 or 2.
Status 0 = Inactive,
Status 1 = Subscribed and
Status 2 = Active.
Now all you have to do is to change this value for it to start working normally. You can use a simple update statement like this -
UPDATE distribution.msdb.MSsubscriptions
SET status = 2
WHERE publisher_db = 'MyPubDB' and
subscriber_db = 'MySubscriberDB' and
status = 0
After running this query wait for a couple of minutes for the replication job to retry and it should be running smoothly. I am still trying to find out the way to completely eliminate this problem so that subscriptions do not expire at all. Will update you soon on that. In the meantime, if you have a solution, you can post it in the comments.
Hope it helps.
Currently I am working on a huge setup of servers which utilizes replication quite extensively. Each of these servers has about 4 to 10 publication and around 10 to 40 Subscribers running 24x7. An issue, I was facing quite regularly on many subscribers was that they tend to expire and then the second step of the replication job simply goes in the retry mode.
I investigated the issue by first finding the replication and then going into one of the tables in 'Distribution' database of my distributor. The table is dbo.MSsubscriptions. It is a very usefull table with the complete info about all the Subscriptions configured on this distributor. It contains the following columns of our interest -
publisher_database_id, publisher_db, subscriber_db and status
if you look for the value of the status column corresponding to the right Publisher and Subscriber Database, it would be either 0, 1 or 2.
Status 0 = Inactive,
Status 1 = Subscribed and
Status 2 = Active.
Now all you have to do is to change this value for it to start working normally. You can use a simple update statement like this -
UPDATE distribution.msdb.MSsubscriptions
SET status = 2
WHERE publisher_db = 'MyPubDB' and
subscriber_db = 'MySubscriberDB' and
status = 0
After running this query wait for a couple of minutes for the replication job to retry and it should be running smoothly. I am still trying to find out the way to completely eliminate this problem so that subscriptions do not expire at all. Will update you soon on that. In the meantime, if you have a solution, you can post it in the comments.
Hope it helps.
Subscribe to:
Posts (Atom)