March 19, 2012 at 4:44 pm
Hello everyone
Looking to remove a subscription manually.
How long can it take to remove a subscription sp_dropsubscription? Is there a way to know how long it will take ?
March 19, 2012 at 4:46 pm
Can I look to remove more then one subscription at once in another query window? Or wait for the first drop subscription to complete
March 20, 2012 at 7:56 am
It is still running from last night aprox about 15 hours....
Any suggestions....
March 20, 2012 at 11:02 am
Depending on how you did it, its quite possible there is another process blocking the drop subscription. Have you checked for a blocking spid?
Otherwise you are completely able to drop multiple subscriptions in the same query batch. Each will complete iteratively.
March 20, 2012 at 12:22 pm
Thank you for that suggestion.
I will take a look and follow up.
March 20, 2012 at 1:20 pm
When running sp_droppublication and it just sits I do not see any blocking spid.
Looked in the activity monitor.
Checked the sys.dm_tran_locks
Checked the sys.dm_os_waiting_tasks....
Any other suggestions.
I have stopped all agent jobs created by creating the transactional replication.
March 20, 2012 at 1:38 pm
Jonathan Marshall (3/20/2012)
When running sp_droppublication
have you removed all of the subscriptions first?
Try these to dynamically generate drops at the article level. Generate all of the statements first or you'll need to edit the second query to remove the syssubscriptions table join.
This generates subscription drops, article by article.
/*insert publication name*/
select 'exec sp_dropsubscription @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @subscriber = N''' + ss.srvname + '''' + char(13) +'GO'
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sp.name in ('')
Then drop the articles themselves once unsubscribed.
select distinct 'exec sp_droparticle @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @force_invalidate_snapshot = 1' + char(10) + 'GO' + char(10)
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sp.name in ('')
Then try dropping the publication.
March 20, 2012 at 1:51 pm
I will give that a try.
Should I wait till the cancelled query is finished or just restart the service?
I cancelled the query in QA and that is just sitting.
The wait type on this operation is OLEDB.
March 20, 2012 at 1:53 pm
I've seen this before where it just says under the command column KILLED/ROLLBACK and can run for over a day. WaitType = OLEDB
The only way I have removed this before is restarting the service.
March 20, 2012 at 3:39 pm
You do need to rollback any currently running queries.
The KILLED/ROLLBACK state is an "it depends" moment. It could be like that for a while and succeed or it might not ever.
I'd ensure nothing is blocking it, make sure you have a good back up and if it still doesnt rollback in xx hours/minutes then consider restarting the service
March 21, 2012 at 10:26 am
When I query the syssubscriptions table there are no rows returned.
I would think that I would get some rows returned
use distribution
select * from syssubscriptions
March 21, 2012 at 10:27 am
When I query the syssubscriptions table there are no rows returned.
I would think that I would get some rows returned
use distribution
select * from syssubscriptions
March 21, 2012 at 10:55 am
Is this server its own distributor? That confirms that the distributor no longer knows about any subscriptions.
Run these statements on the published database. I'd expect nothing from the first one if the distribution database doesnt contain any.
select distinct 'exec sp_dropsubscription @publication = N''' + sp.name + '''' + ', @article = N''' + 'all' + '''' + ', @subscriber = N''' + ss.srvname + '''' + char(10) + 'GO' + char(10)
from syssubscriptions ss join sysarticles sa on sa.artid = ss.artid
join syspublications sp on sp.pubid = sa.pubid
where ss.srvname in ('')
select distinct 'exec sp_droparticle @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @force_invalidate_snapshot = 1' + char(10) + 'GO' + char(10)
from sysarticles sa
inner join syspublications sp on sp.pubid = sa.pubid
where sp.name in ('')
March 21, 2012 at 11:15 am
Yes the server is its own distributor.
Both queries returned nothing.
When I query MSarticles I do results.
When I run select @@servername I get a null returned.
I thought that the distributor doesn't recognize because of this
http://support.microsoft.com/kb/302223
When trying to drop and I could not because of subscriptions or I would call scar tissues still being left over.
March 21, 2012 at 1:17 pm
Very interesting as I see this scenario often around the web.
Wonder what I'm missing.
1). Server Name definitely was changed @@servername returns null
2). When looking to drop and add the server cannot do so because server being used as a Publisher
(Msg 20582)
3). Cannot use the GUI to disable LDB01 as a publisher because right clicking on anything gives the error
Sql Server Replication requires the actual server name to make a connection to the server. Connections through a server alias IP address, or any other alternate name are not supported
Is there a way to disable a server being a publisher through QA so that I can drop and add the server correctly.
The outcome I seek is being able to manually remove the transactional replication so that I can start from scratch.
Will continue to see what I could be missing in this process.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply