Sql-server – Cannot delete publication

If you were looking for the big hammer, sp_removedbreplication is it. This should do what you are asking, and remove all of the replication related goodies in the subscribing database, regardless of whether or not the publisher is accessible. Once the publisher/distributor come back up you can do the cleanup to remove the subscription on that side as well.

Like I said, this is the BIG HAMMER, so use it carefully.

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication  
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO 

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal' 

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!