How to Fix Messed Up Replication in MS SQL Server

replicationsql serversql-server-2012transactional-replicationtroubleshooting

I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-create it (we have a script to re-create it from scratch). I'm not sure exactly what I did, but now it is in a completely messed up state and I can't fix it.

First, I try to get rid of the subscription (on the publisher server):

EXEC sp_dropsubscription @publication = 'PublicationName', @article = N'all', @subscriber = 'SubscriberServerName'

This seems to work. SELECT * FROM syssubscriptions shows no results. Looking on the subscriber server, SSMS > {SubscriberServer} > Replication > Local Subscriptions – the subscription is not there.

So then I try to delete the publication. SSMS > {Server} > Replication > Local Publications > {PublicationName} > Delete. This gives the following error message:

Could not delete publication 'PublicationName'.
Could not drop article. A subscription exists on it.
Changed database context to 'DatabaseName'. (Microsoft SQL Server, Error: 14046)

Ok, so I try to drop the articles:

EXEC sp_droparticle @publication = 'PublicationName', @article = N'all'

and get this error:

Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.
Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

Ok, so I try starting the Snapshot Agent and I get this internal SQL exception:

The SQL command 'sp_MSactivate_auto_sub' had returned fewer rows than expected by the replication agent.

So I tried an alternative method of deleting the article, DELETE FROM sysarticles. This seems to have worked – I have now got rid of the articles, but I still get the same 'Cannot drop the publication because at least one subscription exists for this publication' error when I try to delete the publication.

I have also restarted SQL Server – didn't help.

I don't know what is going on here and how do I fix it?

BTW this is what happens when you give a software developer who knows just enough to be dangerous the keys to the database. Fortunately, this isn't a production environment…

Best Answer

TLDR:

It appears that disabling and re-enabling replication probably fixed the issue:

exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false'
exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true'

I guess this is the equivalent of switching it off and then back on again...

Longer version:

A workmate had a go at trying to fix it. He tried a few things but didn't get very far. The one change he did make before giving up was to disable the replication.

I then tried Cody's suggestion. The sp_dropsubscription command complained that no subscriptions exists. So I tried the sp_droppublication command. This complained that replication was not enabled on the database. So I enabled it and re-ran the command. This time it complained that the publication did not exist. I refreshed the Local Publications node in SSMS and sure enough it had gone. I ran the replication set up script, generated a new snapshot and every thing is now working properly. Joy!

I'm not 100% certain that disabling and enabling the replication is what actually fixed the problem, but it is definitely worth trying if the replication gets messed up.

Related Question