Deleting replication, leaves data in distribution database

Hi,

>From a publisher I replicate 3 databases to a distributor/subscriber
machine. On all of them i have MS SQL 2005.
When i delete one of the replications by running
publisher => sp_dropsubscription
subscriber/distributor=>sp_removedbreplication
publisher => sp_removedbreplication
subscriber/distributor =>sp_subscription_cleanup
On the publisher and subscriptions databases all replication things
are removed.
However when looking with the replication monitor the deleted
replication is visible with a big red cross. Also the jobs are still
in the job list of the distributor!
When removing everything by dropping the distribution database all is
cleared. I know that in SQL 2000 the distributor would also be
cleared. Has this changed?
Is this a bug in SQL2005 or am i forgotting something (i have searched
a lot already, but cannot find anything)?
Hopefully someone can help me?
Marcel
I'd recommend using sp_droppublication which'll clean up the jobs as well.
sp_removedbreplication is something I run only occasionally to clean up any
remaining orphaned objects.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||On 1 feb, 10:28, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I'd recommend using sp_droppublication which'll clean up the jobs as well.
> sp_removedbreplication is something I run only occasionally to clean up any
> remaining orphaned objects.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
Thanks Paul,
This looks much better! I finalized with
EXEC sp_replicationdboption
@.dbname = @.publicationDB,
@.optname = N'publish',
@.value = N'false';
This should remove the publication objects...
Marcel

Deleting an Redundant Subscriber

Hi All

I'm having SQL Server Subscription grief at the mo. I had two severs, one the Distributor / Publisher and the other the Subscriber (Transactional Replication with Queued Updating.)

The Distributor / Publisher crashed unrecoverable and the subscriber became the new Distributor / Publisher and another Subscriber was brought in.

However I have discovered that new Distributor / Publisher is still acting
as a Subscriber to the now non exsistant server. The MSreplication_queue table on the new Distributor / Publisher is now five gig and growing. Viewing this table the entries are queueing up all the data since the server crash in order to send it to the now dead server.

I have run sp_subscription_cleanup but it was not removed the legacy
Subscription tables.

I don't want to run sp_removedbreplication as it will remove ALL replication and not just the Subscriber elements.

How can I get rid of these legacy tables??

Thanks

AliWhen the Publisher/Distributor crashed, did you unsubscribe all subscriptions in the Subscriber and then uninstall Replication?|||Unfortunately not. The Subscriber info was still on the machine when it was installed as the Distributor / Publisher.|||The only way, that I know of, to get rid of the subscription cleanly is to execute sp_dropsubscription. But in your case, sql will not be able to recoginze the publisher/distribution because it's no longer there. Yet the subscription still exists in the server. I would get a server or just a desktop and setup a publisher/distributor using the old publisher/distributor's name, the one that crashed. Remember this has to be in the same domain and it's just like the old one. Then you execute sp_dropsubscription to clean up the old subscriptions.

Hope this helps. Good luck!sql

Deleted 3.3 million records, and now replication hung

I deleted 3.3 million records out of a table on the distributor. They
merged correctly to one subscriber but the other two aren't getting the
deletes. Since the only deletions on that table would be the ones I did,
can I run the following with no adverse effects:
delete from msmerge_tombstone
where tablenick in (select nickname from sysmergearticles where
tablenick=nickname and name='MyTable') and
rowguid not in (select aud_rowguid from MyTable where
aud_rowguid=rowguid)
Darin
*** Sent via Developersdex http://www.codecomments.com ***
While you could do this it is not advisable as you will run into problems
later on down the road. I would drop the subscribers, do the delete and then
redeploy the subscriber(s).
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darin" <darin_nospam@.nospamever> wrote in message
news:%232dmt9pLIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I deleted 3.3 million records out of a table on the distributor. They
> merged correctly to one subscriber but the other two aren't getting the
> deletes. Since the only deletions on that table would be the ones I did,
> can I run the following with no adverse effects:
> delete from msmerge_tombstone
> where tablenick in (select nickname from sysmergearticles where
> tablenick=nickname and name='MyTable') and
> rowguid not in (select aud_rowguid from MyTable where
> aud_rowguid=rowguid)
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***