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

DeleteSubscription leaves job in SQL Server

Has anyone else noticed that if you create a timed subscription using

the web service and then delete it using the DeleteSubscription method,

there is a job (id'ed by a guid) left in SQL Server Jobs?

Consequently we now have hundres of "orphaned" jobs in our database :-(

A bit more investigation appears to reveal this:

I use data driven subscriptions and create them using CreateDataDrivenSubscription. Therefore I retrieve them using GetDataDrivenSubscriptionProperties. The matchData returned does not contain a scheduleId. Is that correct? Well calling ListSchedules returns an empty row, indicating that the schedules created are not shared, so cannot be deleted using DeleteSchedule anyway. However, and this is where the bug appears to be, the schedules are in the ReportSchedule table and the jobs are in SQL Server. Calling DeleteSubscription does not delete the SQL Server Job, nor does it delete the row from ReportSchedule. Is this a bug? Is there a fix or do I have to delete the row myself and delete the job from SQL Server using DMO or some such hack.

|||

The SQL Agent jobs should be deleted when the associated subscription is deleted. There appears to be a problem with your system.

Can you take a peek at Event Viewer and SQLAgent log files and see if there is any incriminating evidence?

Deleted Subscriptiption on Subscriber

Dear Friends
I have deleted the Publication from the publisher but
still the Subscription from the subscriber are not
deleted due to same i am getting error while creating new
Publication as the table can not be droped as the same
are being used by another publication.
Kindly suggest how i can delete the same so that my
replication will start working from the subscriber.
Your earlier reply would be a great help.
Best regards
Sharad
Sharad,
Running sp_removedbreplication can be used to remove all traces of
replication in the subscriber database, but obviously must only be done if
this database is not also configured as a publisher. For a more granular
approach, there is a stored procedure to do this called sp_MSunmarkreplinfo
which takes a tablename as a parameter (alternatively, setting replinfo to 0
in sysobjects for the particular table should do it).
HTH,
Paul Ibison

Delete subscription problem

Hi,
I create both non-data-driven subscription and data-driven subscription in
my application for one report. Is there any way to distinguish those two
subscriptions? So I can delete them, since Reporting Services uses different
functions to delete the non-data-driven subscription and data-driven
subscription.
Thank you very much for your help!
MichelleListSubscriptions returns both types and has a bool which tells you if the
subscription is a data driven subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:E44A9504-2058-4F74-9D47-7473DB69FE94@.microsoft.com...
> Hi,
> I create both non-data-driven subscription and data-driven subscription in
> my application for one report. Is there any way to distinguish those two
> subscriptions? So I can delete them, since Reporting Services uses
> different
> functions to delete the non-data-driven subscription and data-driven
> subscription.
> Thank you very much for your help!
> Michelle|||Thank you!!!
"Daniel Reib (MSFT)" wrote:
> ListSubscriptions returns both types and has a bool which tells you if the
> subscription is a data driven subscription.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
> news:E44A9504-2058-4F74-9D47-7473DB69FE94@.microsoft.com...
> > Hi,
> >
> > I create both non-data-driven subscription and data-driven subscription in
> > my application for one report. Is there any way to distinguish those two
> > subscriptions? So I can delete them, since Reporting Services uses
> > different
> > functions to delete the non-data-driven subscription and data-driven
> > subscription.
> >
> > Thank you very much for your help!
> >
> > Michelle
>
>

Delete Subscription

I am still struggling to delete a subscription so I can set another one to
that server
I believe that normally that would be listed in the Properties Page of the
Publisher, but it doesn't
How can I still delete it
Thank you,
Samuel
Did you try to issue a sp_dropsubscription 'Publicationname','articlename/or
all','Subscribername','subscriptiondatabase'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:ur04P1y4GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I am still struggling to delete a subscription so I can set another one to
>that server
> I believe that normally that would be listed in the Properties Page of the
> Publisher, but it doesn't
> How can I still delete it
> Thank you,
> Samuel
>