"The subscription(s) have been marked inactive and must be reinitialized"

Question

Post reply

"The subscription(s) have been marked inactive and must be reinitialized"

raptorkiller47

SSChasing Mays

Points: 627
More actions
September 28, 2004 at 6:59 pm

#86672

Hi all,
After rebooting the server that run SQL Server 2000, I got error for a few replications. "The subscription(s) have been marked inactive and must be reinitialized. NoSync subcriptions will need to be dropped or recreated".
I managed to reinitialize some of them but there is one particular Transactional Replication that neither I could reinitialize nor delete it. Everytime I tried to either reinitialize or delete it, SQL Enterprise hung. Very much I cant do anything to it at the moment.
Could you please help me out as I am pretty desperate at the moment since I cant find out any possible solution from anywhere.
Thank you very much in advance for any input.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Roust_m SSCoach Points: 17408 More actions · Answer 1

What about using TSQL?

sp_dropsubscription @publication = 'xxx'

, @article = 'yyy'

, @subscriber = 'zzz'

, @destination_db = 'zzz'

raptorkiller47 SSChasing Mays Points: 627 More actions · Answer 2

Hi Roust m,

Thank you very much for your input. Yesterday I searched around and found some script which is similar to what you suggested and somehow, it worked (I say somehow becos I tried those scripts and at first it did not work, although I tried with same procedure but then it worked). Basically what i did was trying to delete the subscription and then publication using the query.

I just have another question if you dont mind, just to avoid the problem to happen again, is there anything that needs to be done before I restart the distributor / publisher or a machine that runs SQL server?

Roust_m SSCoach Points: 17408 More actions · Answer 3

... is there anything that needs to be done before I restart the distributor / publisher or a machine that runs SQL server?

- Not anything that I am aware of. But if the problem repeats, I would try stopping SQL Agent service before rebooting the server or may be even SQL Server service as well.

CoetzeeW Ten Centuries Points: 1361 More actions · Answer 4

Remember that when you drop a Publication or a subscription SQL have to do alot of work in the background ..one is to I call it "unflag" all that tables that is part of the publication , "remove any addional columns that where added when the transactional publication was created - go look nicely at your table structures you should find the addional columns there ...and then it must do exactly the same at the subscriber ...so that possibly why it did not respond ..and if it a a workhorse server then it would even be worse , addional workloads ....

I know you have your answer already and it was a good call by OLD HAND to drop the subscription first .

To your second question what I always do is I stop the log reader agent then the distribution agent .....because the log reader agent may still be reading the log and passing data to the distribution database and at the same time the distribution agent is replicationg the data .....the process will stop as soon as SQL distributed the last transaction that occured on the publisher\distributor .

Cool

Roust_m SSCoach Points: 17408 More actions · Answer 5

what I always do is I stop the log reader agent then the distribution agent .....

Doesn't stopping SQL Agent service stop all the log reader agents as well as distribution agents? If you have a number of published databases, it would be a pain to stop all the log reader and distribution agents, besides you are rebooting the server anyway.

BTW: I am not "OLD HAND" as well as you are not "Grasshopper"...